Sunday, 14 January 2018

Assign exec parameter in a variable using sql query

//////////Funtions/////////

declare @Variable varchar(12)
declare @SqtCommand nvarchar(4000)
set @SqtCommand = 'select @Variable= FiledName from TableName'
exec sp_executeSQl @SqtCommand, N'@Variable varchar(12) output', @Variable output

print @Variable


//////////RESULT////////////


declare @Variable varchar(12)
declare @SqtCommand nvarchar(4000)
set @SqtCommand = 'select @Variable=CAST(MIN(Year)as varchar)+''-''+ cast(MAX(Year) as varchar) from tbl_SurfaceWater inner join mstLocation on tbl_SurfaceWater.LocationGUID=mstLocation.LocationGUID where (Year(GETDATE())-Year) between 0 and 30 and DataTypeCatID=1 and TemplateID=2'
exec sp_executeSQl @SqtCommand, N'@Variable varchar(12) output', @Variable output
print @Variable

/////////Result///////////

No comments:

Post a Comment

How to highlight selected text in notepad++

  –> To highlight a block of code in Notepad++, please do the following steps step-1  :- Select the required text. step-2  :- Right click...