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

Excel Sort values in ascending order using function TEXTJOIN

 Excel ::  Text ::  1,3,5,2,9,5,11 Result :: 1,2,3,5,5,9,11 Formula ::     TEXTJOIN ( ",",1,SORT(MID(SUBSTITUTE( A1 ,","...