November 30, 2009 at 6:05 am
i'm trying to fill a parameter with a select statement.
now i know the following works:
declare @ID integer = (select 1)
but i would like to be able to fill the parameter with what the following code selects, which ,in this basic case, would be '0'.
DECLARE @DocIDOne integer = 0
IF (SELECT ISNULL(@DocIDOne,0)) = 0 BEGIN PRINT 'hello' END
i've tried the following but it gives an error. is this even possible? thanks
DECLARE @DocIDOne integer = 0
DECLARE @ID Integer
IF (SELECT @ID = ISNULL(@DocIDOne,0)) = 0 BEGIN PRINT 'Hello' END
November 30, 2009 at 6:42 am
You're overcomplicating things. No need for the SELECT in that case. You don't need to assign to a separate variable, unless you're going to use that second variable later
DECLARE @DocIDOne integer = 0
IF (ISNULL(@DocIDOne,0)) = 0
BEGIN
PRINT 'Hello'
END
p.s. Those aren't parameters, they're local variables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 7:11 am
aah sorry 🙂 variables.
i'll post abit more of my code so that you can see what it does. this is still a stripped down version to keep it looking clean.
IF NOT EXISTS (SELECT ID --check if record already exists.
FROM TableOne
Where Col1 = @var1
BEGIN
IF @var2 = 1
BEGIN
--Update Record
END
ELSE
BEGIN
IF (SELECT ISNULL(DocIDOne,0) --This is the column i want to fill the variable from
FROM TableTwo
WHERE col2 = @var2)
= 0
BEGIN
EXEC storedProcedure @DocIDOne --this is my proposed variable that was filled from the IF statement above
END
END
END
i'd prefer not to have to do another SELECT statment if i can get the info from a SELECT statement that already exists.
if thats not possible though, i'll just have to make do 🙂
i may have put my BEGIN..ENDS in the wrong place in my hast to post. but hopefully you get the generaly idea of what i'm trying to do
November 30, 2009 at 7:35 am
--check if record already exists.
IF NOT EXISTS (SELECT ID FROM TableOne WHERE Col1 = @var1 )
BEGIN
IF @var2 = 1
BEGIN
--Update Record
END
ELSE
BEGIN
SELECT @DocIDOne = DocIDOne FROM TableTwo WHERE col2 = @var2 -- Will this only return one row?
IF ISNULL(DocIDOne,0) = 0
BEGIN
EXEC storedProcedure @DocIDOne
END
END
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2009 at 7:59 am
ok. that seems the most succinct way of doing it. the select statement will only ever bring back zero or 1 record so it works perfect.
thanks for your help. its much appreciated 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply