sp_executesql

Question

Post reply

sp_executesql

Viv123

SSC Veteran

Points: 292
More actions
April 8, 2013 at 4:38 pm

#276249

Hi Guys,
I want to assign value in @string1 variable to @output in sp_executesql how can i do that .Any help will be highly appreciated.The current statement returns null which is not true.
exec sp_executesql @output=@string1
Regards
Viv

Roshan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The format for sp_executesql is:

exec sp_executeSQL @SQLString, @ParameterDeclaration, @Output = @Variable

Exact details in Books Online.

Without seeing what you're doing, that's about the best I can offer

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viv123 SSC Veteran Points: 292 More actions · Answer 2

Here's my query:

This is my first statement:-

set @string1= 'select MAX(backup_set_id)

FROM ['+@server_name+'].msdb.dbo.backupset

WHERE database_name = '''+@db+''' AND type = ''D'''

Second Statement:-

exec sp_executesql @string1, N'@item nvarchar(15) OUTPUT', @output = @item OUTPUT

select @output

I want to capture @output and use it later in a join

Roshan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

DECLARE @string1 NVARCHAR(1000)

DECLARE @Output NVARCHAR(15)

SET @String1 = 'select @item = MAX(backup_set_id)

FROM ['+@server_name+'].msdb.dbo.backupset

WHERE database_name = '''+@db+''' AND type = ''D'''

exec sp_executesql @string1, N'@item nvarchar(15) OUTPUT', @item = @output OUTPUT

select @output

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viv123 SSC Veteran Points: 292 More actions · Answer 4

Thx for the query .But its returning null.@string contains value 652127 but @output shows null

Roshan

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Post ALL your code, dynamic sql and the sql used to call it.

Viv123 SSC Veteran Points: 292 More actions · Answer 6

i got it.but i have another problem.I have got the server name in a variable @servername.and i want to use it to query other servers msdb (other sql servers are added as linked server).

what would be the syntax

when i run this query i get err:- Could not find server ''+@server_name+'' in sys.servers

select b.* FROM ['+@server_name+'].msdb.dbo.backupset b,

['+@server_name+'].msdb.dbo.backupmediafamily mf,

Roshan