How to expand @variables in SQL Statement
-
Using transact-sql, I want to select records based upon a parameter value.
If I pass 1, I will show only Active users. Otherwise I default to 0.
Here is a snippet of code:
create proc spListEmployees (@Active smallint = 0)
as
Declare @NoOfEmployee integer
Declare @Include varchar(10)
if @Active = 1
Set @Include = ' ''A'' '
else
Set @Include = ' ''A'',''I'' '
select @NoOfEmployee = count(*) from employee where status in (@Include)
print 'NoOfEmployee=' + Cast (@NoOfEmployee as varchar(10))
GO
T-SQL does not like @Include. The Select statement does not returns any rows although rows exist.
Thanks.
-
Have you tried WHERE Status = @Include?
If that fails you will have to create teh entire SQL string as a variable and then
sp_executesql @SQLString
Good Hunting!AJ Ahrens
webmaster@kritter.net -
You may also want to try assigning @Include with ASCII characters.
SELECT @Include = CHAR(34) + 'A' + CHAR(34) + ', ' + CHAR(34) + 'I' + CHAR(34)
Sometimes the double quote gets a little screwy...
I wasn't born stupid - I had to study.
-
I apologize. I missed the setting of the @Include to a variable like 'A', 'I'
You will need to build your SQL string and then EXEC it or run sp_executesql.
Hopefully in a future release of SQL performing queries like this will be easier
Good Hunting!AJ Ahrens
webmaster@kritter.net -
What you can do is to create two variables and use them to store your codes.
declare @SOPNum as varchar(30)
declare @SOPNum1 as varchar(30)
set @SOPNum = 'STDINV2256'
set @SOPNum1 = 'ORDST2225'
select * from sop10100 hdr
where hdr.sopnumbe in( @SOPNum, @SOPNum1)
or in your example
create proc spListEmployees (@Active smallint = 0)
as
Declare @NoOfEmployee integer
Declare @Include varchar(10)
Declare @Include2 varchar(10)
if @Active = 1
Set @Include = 'A'
else
BEGIN
Set @Include = 'A'
Set @Include2 = 'I'
END
select @NoOfEmployee = count(*) from employee where status in (@Include, @Include2)
print 'NoOfEmployee=' + Cast (@NoOfEmployee as varchar(10))
-
Wow! What is it with you guys and wanting to do it the hard way?!
create proc spListEmployees (@Active smallint = 0) as Declare @NoOfEmployee integer if @Active = 1 select @NoOfEmployee = count(*) from employee where status in ('A') else select @NoOfEmployee = count(*) from employee where status in ('A','I')print 'NoOfEmployee=' + Cast (@NoOfEmployee as varchar(10))
GO
Julian Kuiters
juliankuiters.id.au -
Or you may also do it like this:
create proc spListEmployees (@Active smallint = 0) as Declare @NoOfEmployee integer
select @NoOfEmployee = count(*) from employee where ((status in ('A')) and (@Active = 1)) or ((status in ('A','I')) and (@Active <> 1))print 'NoOfEmployee=' + Cast (@NoOfEmployee as varchar(10))
GO
-
I have to give a hearty 'me too' to what Julian Kuiters said. Don't use dynamic SQL unless you absolutely have to! - and even if you think you have to, think again!
Even better to my mind would be to have TWO stored procedures, one called spListEmployeesAll, the other called spListEmployeesActive, with obvious implentations. Less cryptic at the client end, easier for SQL Server to cache execution plans, just better code!
-
Here is yet another way. I got this technique from another post years ago on this website. It utilizes a dynamic where statement.
if @Active = -1
Set @Include = 'B'
else if @Active = 1
Set @Include = 'A'
else
Set @Include = 'I'
select @NoOfEmployee = count(*)
from employee
where (Case When @Include = 'B' Then 1
When status = @Include Then 1
Else 0 End)=1
I am using -1 to indicate that all records should be counted. This will now act like a switch allowing you to count Active, Inactive or Both.
-
create proc spListEmployees (@Active smallint = 0)
as
Declare @NoOfEmployee integer
select @NoOfEmployee = count(*)
from employee
where charindex(status,substring('IA',@Active+1,2)) > 0
print 'NoOfEmployee=' + Cast (@NoOfEmployee as varchar(10))
GO
Far away is close at hand in the images of elsewhere.
Anon. -
To all who have a better way of doing this....
I unfortunately, am ingrained in certain HARD ways of doing things.... I also look for the easy way and the smart way of doing things unfortunately, when you get to be my age you lose track of things....
Where was I???.. oh yeah, with SQL there is ALWAYS more than a single way of doing things...
As someone else here has in their signature: test, test, and more test...
And please give me a little slack, according to my picture I'm only 3
(my daughter)
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Variables may be not only CHAR type.
declare @Include table (I char)
insert @Include values('A')
select count(*) from employee
where Status in (select I from @Include)
insert @Include values('I')
select count(*) from employee
where Status in (select I from @Include)
And not only 2 statuses to be used in this script.
And you can acually can store (if you want) the default set of parameters in table IncludeSet (I char), insert or delete some values from the set, or do a lot of other fun.
_____________
Code for TallyGenerator -
AJ Ahrens,
I'm not saying anyone's suggestion is wrong, infact some of them are quite inventive, just that they are not the simplest answers.
T-SQL is a simple language, and the optimiser works best with simple statements. The example I gave clear, concise, easy for a developer and the sql optimiser to understand the logic fork. Some of the other examples people have provided are complex, longer, and undoubtably slower.
Again, I'm not saying I'm always right (infact one of the reasons I participate in user forums is they challenge my own thoughts/processes and make me think outside my box).
Sometimes sp_executesql is the ONLY way to get something done due to T-SQL's lack of consistant @variable support. Hell, Microsoft's T-SQL even uses different keyword spellings in places (TRUNCATEONLY and TRUNCATE_ONLY).
K.I.S.S .... (Not the band, the life rule) should be everyone's 1st rule. They're 2nd rule should be K.I.S.S. (For the 3rd Rule, go rent Fight Club).
As a dba/developer who is constantly reading other peoples code: I don't care how much documentation / commenting there is. Bad code is bad code... and a KISS will always make you happy.
Julian Kuiters
juliankuiters.id.au -
Agreed, simply is good and remember some basics. I think one of the options used a function on the column before testing ! Optimisers don't like this and are unlikely to utilise available indexes.
Sometimes simple approaches look more verbose in terms of lines of code, but believe me, several months down the line having to support verbose but logical-to-read code is much less frustrating than trying to work out algorithms which were only there to save lines of code. Optimisers also like things simple !
On a similar but different tack ...
I had a developer mention that he was going to code a windows service to simply interrogate a column value and depending on the result, execute a stored proc. I suggested that he could use the job engine and he thought it was a great idea as it would save him coding ! A no-brainer I thought, but then SQL is my domain, vb asp .net etc is his. His idea was probably simple to him as was mine to me !
Rick
Learn principles, not syntax. Principles rarely change, methods on how to apply them are never static. You can look-up Syntax.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply