Question with variables

Question

Question with variables

DBA-640728

SSChampion

Points: 12898
More actions
May 18, 2009 at 9:59 am

#132288

hi gurus, i have a question, i have a stored procedure which gets execute every minute, which i have to convert all literal to variables. Example
Select distributorid, distributorname, distributorstatus,distributorcargo
from Dist_Main M inner join Dist_Details D on
M.DistPK = D.DistFK
where distribuborcargo = '5'
and distributorstatus in ('9', '7','5')
final:
declare @cargo int
set @cargo = 5
Select distributorid, distributorname, distributorstatus,distributorcargo
from Dist_Main M inner join Dist_Details D on
M.DistPK = D.DistFK
where distribuborcargo = @cargo
and distributorstatus in ('9', '7','5','1')
my issue is creating a variable for distributorstatus in ('9', '7','5','1')
i know i can create a table variable or a temp table, but due to some issues we have in prod i really would like to prevent that, is there another way?

Viewing 7 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

There may be other alternatives, but the only two I can think of are to create a temorary (dynamic) table and join to it in the FROM clause or create ana execute a dynamic query inside the stored procedure.

FelixG SSCrazy Points: 2550 More actions · Answer 2

declare @cargo int
set @cargo = 5
Select distributorid, distributorname, distributorstatus,distributorcargo
from Dist_Main M inner join Dist_Details D on
M.DistPK = D.DistFK
where distribuborcargo = @cargo
and distributorstatus in ('9', '7','5','1')

You should consider the use of dynamic SQL, your variable might be of the varchar type e.g.

declare @status varchar(max),

@cargo int,

@sql varchar(max)

set @cargo = 5;

set @status='9,7,5,1';

set @status= replace (@status,',',''',''')

set @status='('''+@status+''')'

set @sql= 'Select distributorid, distributorname, distributorstatus,distributorcargo

from Dist_Main M inner join Dist_Details D on

M.DistPK = D.DistFK

where distribuborcargo = '+ cast(@cargo as varchar)+'and distributorstatus in '+@status

exec(@sql)

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

FelixG (5/18/2009)
declare @cargo int
set @cargo = 5
Select distributorid, distributorname, distributorstatus,distributorcargo
from Dist_Main M inner join Dist_Details D on
M.DistPK = D.DistFK
where distribuborcargo = @cargo
and distributorstatus in ('9', '7','5','1')
You should consider the use of dynamic SQL, your variable might be of the varchar type e.g.
declare @status varchar(max),
@cargo int,
@sql varchar(max)
set @cargo = 5;
set @status='9,7,5,1';
set @status= replace (@status,',',''',''')
set @status='('''+@status+''')'
set @sql= 'Select distributorid, distributorname, distributorstatus,distributorcargo
from Dist_Main M inner join Dist_Details D on
M.DistPK = D.DistFK
where distribuborcargo = '+ cast(@cargo as varchar)+'and distributorstatus in '+@status
exec(@sql)

If you go the dynamic sql direction, be sure to validate the data being sent in to the procedure to protect against SQL INJECTION attacks.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

I'm not sure why you'd need to move those values into variables, unless you are converting your query to one that takes paramamters. Additionally, using a split function to populat a temp table/table variable is probably your most effiecent option if you need to handle dynamic lists. But, there are other methods. Here is one way that you can avoid dynamic SQL, but I wouldn't use it in an sort of high volume or large table scenario: DECLARE @Foo TABLE (ID INT, StatusID INT)

INSERT @Foo

SELECT 1, 1

UNION ALL SELECT 2, 2

UNION ALL SELECT 3, 3

UNION ALL SELECT 4, 4

UNION ALL SELECT 5, 5

UNION ALL SELECT 6, 3

UNION ALL SELECT 7, 4

UNION ALL SELECT 8, 5

DECLARE @StatusList VARCHAR(4000)

SET @StatusList = ',' + '5,1,2' + ','

SELECT *

FROM @Foo

WHERE @StatusList LIKE '%,' + CAST(StatusID AS VARCHAR(15)) + ',%'

WayneS SSC Guru Points: 95460 More actions · Answer 5

DBA (5/18/2009)
Select distributorid, distributorname, distributorstatus,distributorcargo
from Dist_Main M inner join Dist_Details D on
M.DistPK = D.DistFK
where distribuborcargo = @cargo
and distributorstatus in ('9', '7','5','1')
my issue is creating a variable for distributorstatus in ('9', '7','5','1')
i know i can create a table variable or a temp table, but due to some issues we have in prod i really would like to prevent that, is there another way?

How about XML?

see this article[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DBA-640728 SSChampion Points: 12898 More actions · Answer 6

thanks, i will try your ways, just to give you some info these are sps that pass every minute and we were having issues with the caches.

thanks again.