Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Stored Procedure Question

Expand Messages
  • Andy Mills
    Thanks Norman From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab Sent: Sunday, April 15, 2012 3:12 PM
    Message 1 of 9 , Apr 16, 2012
    • 0 Attachment
      Thanks Norman

      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
      Sent: Sunday, April 15, 2012 3:12 PM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: Re: [SQLQueriesNoCode] Stored Procedure Question



      Try this:

      SELECT sysobjects.name, REPLACE(syscomments.text, 'CREATE PROC', 'ALTER PROC') FROM sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
      WHERE sysobjects.xtype = 'P'
      AND syscomments.text LIKE '%ShortDescription%'
      AND syscomments.text LIKE '%OrderDetails%'

      It will serve as a start step towards your desired solution.
      You can modify this query to cleverly use REPLACE function to replace varchar(200) with varchar(1000).

      Best Wishes,
      Noman Aftab


      http://corpus.quran.com/wordbyword.jsp

      ________________________________
      From: Andy Mills <amills@...<mailto:amills%40jplcreative.com>>
      To: "SQLQueriesNoCode@yahoogroups.com<mailto:SQLQueriesNoCode%40yahoogroups.com>" <SQLQueriesNoCode@yahoogroups.com<mailto:SQLQueriesNoCode%40yahoogroups.com>>
      Sent: Monday, 9 April 2012 11:12 PM
      Subject: [SQLQueriesNoCode] Stored Procedure Question



      I am working on a bug for my current project and tracked down the issue to a column having the wrong size.

      We have dbo.Products.ShortDescription set to VARCHAR(1000) and this value is used to populate dbo.OrderDetails.ShortDescription when a user orders an item (please note: I am aware we are storing the same information in two locations, adding complexity to this issue, but it was done years ago and I have no control over this at this time). The column for dbo.OrderDetails.ShortDescription is set to VARCHAR(200) so in essence, the dbo.OrderDetails.ShortDescription field is only getting the first 200 characters.

      I know to fix this issue, we first need to match the column sizes and update the data.

      My question though is: is there a way to find inside stored procedures where "ShortDescription" or "OrderDetails" is used inside the stored procedure so I can update the variables inside from VARCHAR(200) to VARCHAR(1000) to prevent this from occurring in the future? I know we have an "InsertStoreDetails" procedure that needs updated, but the site has about 150 stored procedures and I didn't want to have to open every one.

      Is there some query I can run to find this information, or some way to avoid having to open each and every stored procedure?

      Thanks
      Andy

      [Non-text portions of this message have been removed]

      [Non-text portions of this message have been removed]



      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.