Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
viperbyte
Posting Yak Master
132 Posts |
Posted - 2013-08-23 : 09:00:12
|
Good morning all,I'm trying to write a stored procedure. I wrote the following Psuedocode to show you folks the idea that I need for a Crystal Report I'm doing. My first question is: Is this somewhat supported by T-SQL, or am I going about this all wrong?My second question is: If this is supported, will someone super please guide me with the Syntax?--Parameter passed in from Crystal--ParmCat ->CATEGORY--ParmItem ->ITEMbegin case ParmCat = "CC" if parmItem = "ITEM" select * from aTable where Category = 'CC' and Item in ('cc110','cc220','cc240RF') else select * from aTable where Category = 'CC' and Item = ParmItem case ParmCat = "VP" if parmItem = "ITEM" select * from aTable where Category = 'VP' and Item in ('vp10d','vp10de','vp12'0 else select * from aTable where Category = 'VP' and Item = ParmItem case ParmCat = "ALL" select * from aTable where Category = 'CC' and Item in ('cc110','cc220','cc240RF') union select * from aTable where Category = 'VP' and Item in ('vp10d','vp10de','vp12'0end begin |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-23 : 10:54:53
|
you can use dynamic SQL to achieve your results like this:[CODE]CREATE PROCEDURE MyProcedure -- Add the parameters for the stored procedure here @p1 VARCHAR(16) = 'CC', --ParmCat ->CATEGORY @p2 VARCHAR(16) = 'cc110' --ParmItem ->ITEMASBEGIN DECLARE @SQLString VARCHAR(2000) = 'SELECT ''No Valid Data Selected'''; -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT @SQLString = CASE WHEN @p1 = 'CC' THEN (CASE WHEN @p2 = 'ITEM' THEN 'select * from aTable where Category = ''CC'' and Item in (''cc110'',''cc220'',''cc240RF'')' else 'select * from aTable where Category = ''CC'' and Item = ParmItem' END) WHEN @p1 = 'VP' THEN (CASE WHEN @p2 = 'ITEM' THEN 'select * from aTable where Category = ''VP'' and Item in (''vp10d'',''vp10de'',''vp12'')' else 'select * from aTable where Category = ''VP'' and Item = ParmItem' END) WHEN @p1 = 'ALL' THEN 'select * from aTable where Category = ''CC'' and Item in (''cc110'',''cc220'',''cc240RF'')' + ' UNION ' + 'select * from aTable where Category = ''VP'' and Item in (''vp10d'',''vp10de'',''vp12'')' ENDEXEC (@SQLString);end;GOEXEC MyProcedure; DROP PROCEDURE MyProcedure;[/CODE] |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-08-23 : 13:25:21
|
From a run-time efficiency standpoint, dynamic SQL may work better.But you can code it into a single SQL query if you prefer:SELECT a.*FROM dbo.aTable aWHERE (@ParmCat = 'ALL' OR a.Category = @ParmCat) AND ((@ParmItem = 'Item' AND a.Item IN ('cc110', 'cc220', 'cc240RF', 'vp10d', 'vp10de', 'vp12')) OR (@ParmItem <> 'Item' AND a.Item = @ParmItem)) |
|
|
viperbyte
Posting Yak Master
132 Posts |
Posted - 2013-08-23 : 16:00:58
|
Thanks guys. |
|
|
|
|
|
|
|