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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can This be done?

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

begin
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'0
end 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 ->ITEM
AS
BEGIN
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'')'
END
EXEC (@SQLString);
end;

GO

EXEC MyProcedure;

DROP PROCEDURE MyProcedure;

[/CODE]
Go to Top of Page

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 a
WHERE
(@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))

Go to Top of Page

viperbyte
Posting Yak Master

132 Posts

Posted - 2013-08-23 : 16:00:58
Thanks guys.
Go to Top of Page
   

- Advertisement -