Stored Procedure.
-
Im working on a Stored Procedure to Return me 2 values. I have 5 inputs in my S.P.
What im trying to do is check if my 5(numbers) have winning Matches in my DB.
What I did.
CREATE PROCEDURE test (@ID int,@1 int,@2 int,@3 int,@4 int,@5 int,Match smallint output, Mystery smallint output) AS
DECLARE @Win1 smallint, @Win2 smallint, @Win3 smallint, @Win4 smallint, @Win5 smallint,@Win6 smallint, @Mystery smallint
SELECT @Win1 = NUM1, @Win2 = NUM_2, @Win3 = NUM3, @Win4 = NUM4, @Win5 = NUM5, @Mystery = M_1
FROM DRAW WHERE DRAWID = @ID
Match = Check my Numbers against the Winning Numbers
Mystery = Check my Numbers against Mystery Numbers
I am plannning to use a Case statements.
Could anyone help me out on this..
Thanks in advance.
-
Will u be bit more clear...?
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
Ur nos are creating win win mysterious scenario...hope u hv understood my doubt...explain in det...
Cheers,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
-
I got 5 Numbers...These are my inputs.
I want to check with my DB that whether these Numbers are winning Numbers.
That is why I have a variable Match that returns me the Number of Matching Numbers
And mystery which returns me if My numbers match the Mystery number.
Hope this is clear
-
I'm not sure this is what you need but let's see :
CREATE PROCEDURE dbo.test (@ID int,@1 smallint,@2 smallint,@3 smallint,@4 smallint,@5 smallint, @MystNumber int, @Match bit output, @Mystery bit output) AS
SET NOCOUNT ON
SELECT @Match = case when
@1 = NUM1 AND
@2 = NUM2 AND
@3 = NUM3 AND
@4 = NUM4 AND
@5 = NUM5
THEN 1 ELSE 0 END,
@Mystery = case when @MystNumber = M_1 THEN 1 ELSE 0 END
FROM dbo.DRAW WHERE DRAWID = @ID
SET NOCOUNT OFF
GO
-
@1 = NUM1 AND
@2 = NUM2 AND
@3 = NUM3 AND
@4 = NUM4 AND
this checks my @1 with Num1 only. I want to check my @1-5 with Every Num1 to see if they are any possible matches.
-
Just make sure that the date is kept is numerical order in the db, and that you send the parameters in numerical order. Unless the order is relevant to the draw??
-
Or you could always do this, but it would be 3 to 5 times slower :
NUM1 in (@1, @2, @3, @4, @5) AND
NUM2 in (@1, @2, @3, @4, @5) AND
NUM3 in (@1, @2, @3, @4, @5) AND
NUM4 in (@1, @2, @3, @4, @5) AND
NUM5 in (@1, @2, @3, @4, @5) AND
-
declare @t table(ID int identity(1,1),Num1 int,Num2 int ,Num3 int,Num4 int, Num5 int)
declare @n1 int,@n2 int,@n3 int,@n4 int,@n5 int ,@mis int
select
@n1=21,
@n2=33,
@n3=34,
@n4=45,
@n5=46,
@mis=35
insert into @t
select 1,2,3,4,5 UNION ALL
select 1,5,7,9,15 UNION ALL
select 1,23,44,45,46 UNION ALL
select 11,12,13,14,15 UNION ALL
select 21,22,23,24,25 UNION ALL
select 21,32,33,34,35 UNION ALL
select 21,33,34,45,46
select *,
case when Num1 in ( @n1,@n2,@n3,@n4,@n5) then ltrim(str(Num1)) when Num1=@mis then ltrim(str(Num1))+'*' else '--' end as FOUND1,
case when Num2 in ( @n1,@n2,@n3,@n4,@n5) then ltrim(str(Num2)) when Num2=@mis then ltrim(str(Num2))+'*' else '--' end as FOUND2,
case when Num3 in ( @n1,@n2,@n3,@n4,@n5) then ltrim(str(Num3)) when Num3=@mis then ltrim(str(Num3))+'*' else '--' end as FOUND3,
case when Num4 in ( @n1,@n2,@n3,@n4,@n5) then ltrim(str(Num4)) when Num4=@mis then ltrim(str(Num4))+'*' else '--' end as FOUND4,
case when Num5 in ( @n1,@n2,@n3,@n4,@n5) then ltrim(str(Num5)) when Num5=@mis then ltrim(str(Num5))+'*' else '--' end as FOUND5
from @t
where
case when Num1 in ( @n1,@n2,@n3,@n4,@n5) then 1 else 0 end+
case when Num2 in ( @n1,@n2,@n3,@n4,@n5) then 1 else 0 end+
case when Num3 in ( @n1,@n2,@n3,@n4,@n5) then 1 else 0 end+
case when Num4 in ( @n1,@n2,@n3,@n4,@n5) then 1 else 0 end+
case when Num5 in ( @n1,@n2,@n3,@n4,@n5) then 1 else 0 end+
case when @mis in ( Num1,Num2,Num3,Num4,Num5) then 1 else 0 end
>=3
Kindest Regards,Vasc
-
its not returning me with any values if I use this???
-
Stupid, direct, obvious question here... How am I supposed to know what you want the sp to return if you show me explicitly what you need?
Read this so that I can give the right answer the first time next time : Help us help you
-
What I want my SP to return is 2 Values. Which are my @Match and @Mystery. There values are the Number of Matches found.
I have 5inputs, which are my Numbers that I have Bet and DrawID.
Now in my Draw Table I have 5 Winning Numbers and 1 Mystery Number.
So what i need is to take my 5inputs and Check them against my 5 Winning Numbers. If there are any Matches, I should store the no.of Matches into the @Match and output it.
Same thing again, I need to take my 5inputs and Check them again my 1 Mystery Number. If there is any Match I shoud store the no.of Matches into the @Mystery and output it
Hope you get a clearer picture..
-
Read the link and follow the instructions. I'm ready to help you but you have to give me the info I need (SAMPLE DATA/RESULTS).
-
CREATE TABLE [DRAW] (
[DRAWID] [int] IDENTITY (1, 1) NOT NULL ,
[DRAWDATE] [smalldatetime] NOT NULL ,
[NUM1] [smallint] NULL ,
[NUM2] [smallint] NULL ,
[NUM3] [smallint] NULL ,
[NUM4] [smallint] NULL ,
[NUM5] [smallint] NULL ,
[NUM6] [smallint] NULL ,
[MYSTERY_NUMBER] [smallint] NULL ,
CONSTRAINT [PK__DRAW__014935CB] PRIMARY KEY CLUSTERED
(
[DRAWID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
-
This is the most important part of my request : SAMPLE DATA/RESULTS.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply