March 24, 2011 at 1:47 pm
I have three table X,Y and Z
TAble X
ID Record
1 1
2 3
3 4
TAble Y
ID Record
1 2
2 5
3 6
TAble Z
ID Record
1 7
2 8
3 9
I want to write a procedure that selects ecord based on GroupSelected. Example If GroupX then 1,3,4
Group Y Then 2,5,6
ALTER PROCEDURE usp_SelectBins
@BinGroup VARCHAR(10)
AS
BEGIN
SELECT BIN FROM (
SELECT
SUBSTRING(txn.pan, 1, 6) AS Bin,
CASE WHEN @BinGroup = X' THEN ( SELECT Record FROM X)
WHEN @BinGroup = 'Y' THEN (SELECT Record FROMY )
WHEN @BinGroup = 'Z' THEN (SELECT Record FROM Z)
END AS BinGroup
FROM [dbo].[A] txn ) Final
WHERE Final.BinGroup = @BinGroup
END
But thisdoesnot return any values
Please help
March 24, 2011 at 2:01 pm
what is in the dbo.[A] table? does it have any rows in it?
Try eliminating the "from dbo.[A]...." and see what happens. but I am thinking what you need is a simple if statement:
if @BinGroup = X'
select . . . . from X
else if @BinGroup = 'Y'
select . . . . from Y
...
or perhaps just create a dynamic SQL query.
The probability of survival is inversely proportional to the angle of arrival.
March 24, 2011 at 2:07 pm
Typically for something like this, you would need to use dynamic SQL.
You are also missing a single quote that may be affecting your query.
Here is something that may help as well.
Declare @BinGroup VARCHAR(10)
Set @BinGroup = 'z'
;
with tablex (ID, record) as (
Select 1,1
Union all
Select 2,3
Union all
Select 3,4
), tabley (ID, record) as (
Select 1,2
Union all
Select 2,5
Union all
Select 3,6
), tablez (ID, record) as (
Select 1,7
Union all
Select 2,8
Union all
Select 3,9
), alltogether as (
Select ID,record,'X' as SourceTab
From tablex
Union All
Select ID,record,'Y' as SourceTab
From tabley
Union All
Select ID,record,'Z' as SourceTab
From tablez)
Select 'blah' as Bin,Record
From alltogether
Where SourceTab = @BinGroup
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 24, 2011 at 2:38 pm
Thanks Jason that worked ,
Now I need in the main query when the parameter Group(X,Y,Z) Say Z is passed then I get the values accordingly for record based on the last query. I need two datasets for the report.
How Do I build the main query based on the above query ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply