SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_executesql


sp_executesql

Author
Message
Viv123
Viv123
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 97
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213509 Visits: 46260
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
Viv123
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 97
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213509 Visits: 46260
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
Viv123
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 97
Thx for the query .But its returning null.@string contains value 652127 but @output shows null


Roshan
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90704 Visits: 38945
Post ALL your code, dynamic sql and the sql used to call it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Viv123
Viv123
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 97
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search