Can i set a value to a variable using executesql

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

General SQL Server Forums

New to SQL Server Programming

Can i set a value to a variable using executesql

Author

Topic

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2013-05-29 : 20:49:08

Hi I am trying to set a value to a variable using dynamic execute sql, but when i run it it is not setting any value to the variable and the variable is being null when i select it, can anyone please help me how to get this to work.
Declare @pField_Value Varchar(500)='Insurance'
Declare @vResult Tinyint
,@vScript Varchar(500)
,@vJoin1 Varchar(500)
,@vJoin2 Varchar(500)
,@vJoin3 Varchar(500)
,@vJoin4 Varchar(500)
,@vSet1 nVarchar(4000)
,@vSet2 nVarchar(4000)
,@vSet3 nVarchar(4000)
,@vSet4 nVarchar(4000)

Select @vScript = 'SELECT'+ Cast(@vResult As Varchar(150))+' = 1
From Table1 O
Inner Join PAYMENT_INFO P on'
,@vJoin1 = 'on (O.Info_Id = P.Info_ID AND Payment_Mode = '+@pField_Value+')
Where Service_Order_ID = @pOrder_Id'
,@vJoin2 = 'on (O.SecondaryInfo_Id = P.Payment_Info_ID)
Where Payment_Mode = @pField_Value AND Service_Order_ID = @pOrder_Id'
,@vJoin3 = 'on (O.Tertiary_Info_Id = P.Payment_Info_ID)
Where Payment_Mode = @pField_Value AND Service_Order_ID = @pOrder_Id'
,@vJoin4 = 'on (O.Other_Info_Id = P.Payment_Info_ID)
Where Payment_Mode = @pField_Value AND Service_Order_ID = @pOrder_Id'

Select @vSet1 = @vScript + ' ' + @vJoin1
,@vSet2 = @vScript + ' ' + @vJoin2
,@vSet3 = @vScript + ' ' + @vJoin3
,@vSet4 = @vScript + ' ' + @vJoin4

exec sp_executesql @vSet1

Select @vResult

If ISNULL(@vResult,0) <> 0
Begin
exec sp_executesql @vSet2
End

If ISNULL(@vResult,0) <> 0
Begin
exec sp_executesql @vSet3
End

If ISNULL(@vResult,0) <> 0
Begin
exec sp_executesql @vSet4

End

Select @vResult

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2013-05-29 : 20:54:29

I am sorry the last statement was select ISNULL(@vResult,0)

But when i check the table it did have the insurance payment so it should be one

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:02:47

see

http://msdn.microsoft.com/en-us/library/ms188001.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources