Stored Procedure Help - Passing a parameter into the ''In'' statement

Question

Post reply

Stored Procedure Help - Passing a parameter into the ''In'' statement

Rob Lindley-469895

Grasshopper

Points: 17
More actions
July 26, 2007 at 1:33 pm

#179939

Hi all.
I need some help if possible. I am running a VB front end that passes a parameters into a Stored Procedure to action an update query.
The SP is:
CREATE PROCEDURE [Developer].[spCR_UpdateDatabase]

@IDPARAM VARCHAR(8000),
@ID_AGENT VARCHAR(50),
@SUPPLIERID INT

AS

DECLARE @QRY VARCHAR(8000)

     SET @QRY = '
           UPDATE
                 tblCR_MainData
           SET
                 RETURNED = 1,
                 RETURNED_DATE = '+ CONVERT(VARCHAR(10), GETDATE(), 103) +',
                 RETURNED_BY = '+ @ID_AGENT +',
                 ID_SUPPLIER = '+ @SUPPLIERID +'
           WHERE
                 ID_RECORD In('+ @IDPARAM +')'

EXEC (@QRY)

GO
To execute this SP the SQL is:
EXECUTE spCR_UpdateDatabase '1,2', 'AGENTNAME', 04
The query is updating records defined in the @IDPARAM variable. E.g 1, 2 will update ID_RECORDS 1 and 2.
The problem is that it's recognising the @ID_AGENT variable as a column name and not text. I've set the whole query in a variable because that's the only way i could get the In statement to work.
If anyone knows a better way to execute this query that would work would be much appricated.
Many Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try this:

SET @QRY = '

UPDATE

tblCR_MainData

SET

RETURNED = 1,

RETURNED_DATE = '+ CONVERT(VARCHAR(10), GETDATE(), 103) +',

RETURNED_BY = '+ @ID_AGENT +',

ID_SUPPLIER = '''+ @SUPPLIERID +'''

WHERE

ID_RECORD In('+ @IDPARAM +')'

Sergiy SSC Guru Points: 110209 More actions · Answer 2

Search this forum for "split function".

Pick one you like the most and convert your @IDPARAM to temp table.

Then you can get rid of dynamic SQL:

UPDATE D

SET

RETURNED = 1,

RETURNED_DATE = GETDATE(),

RETURNED_BY = @ID_AGENT,

ID_SUPPLIER = @SUPPLIERID

FROM dbo.tblCR_MainData D

INNER JOIN dbo.SplitFunction (@IDPARAM) S ON D.ID_RECORD = S.ReturnValue

_____________
Code for TallyGenerator

Rob Lindley-469895 Grasshopper Points: 17 More actions · Answer 3

Thanks a lot for that. Feel a bit stupid for not spotting that but there you go!

Rob Lindley-469895 Grasshopper Points: 17 More actions · Answer 4

Thanks for your reply. I'm always really conscious about server resources when creating tables to run queries, especially if they can be done in one script/SP. I'd be interested to know why you prefer the split function method, I might learn something.

I'll look them up though. Thanks again.

Rob

Sergiy SSC Guru Points: 110209 More actions · Answer 5

Don't be concious.

In VERY many cases using of temporary table takes much less resources than single script.

Look at execution plans of your queries. Every "hash join" you see in there is another temporary table in tempdb. Every "bookmark lokup" - another one.

You don't see them - it does not mean they are not there.

Every "IN" statement creates implicit temporary table. You wish it or not. So, you don't save anything there.

Try my suggestion - I bet it will be faster and more efficient than your original approach.

And if it's faster - then less resources have been used.

_____________
Code for TallyGenerator