question

NickCoronado-4758 avatar image
0 Votes"
NickCoronado-4758 asked MelissaMa-msft commented

Stored Procedure

I’m a bit clueless writing this stored procedure. I want to be able to execute the stored procedure to change the name of any server in my sql_servers table. So I wrote this:

Create proc chngservername
@keyid int, @srvname nvarchar(120)
As
Begin
Update dbo.sql_servers set name = @srvname
Where keyid = @keyid
End
Go

Exec chngservername ‘blue’, 87119

But that gives me an error that it can’t convert an nvarchar to an integer.

What am I doing wrong?

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NickCoronado-4758,

Could you please provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

Thanks for the answers! Here’s an update…I simplified it too much. The stored procedure has to search through all the tables in a db…there’s only three tables thankfully, and update the value to what I pass into the query. Any ideas?

0 Votes 0 ·

Hi @NickCoronado-4758,

Thanks for your update.

Since your original issue has been fixed, it is recommend for you to post a new question with new requirement together with all details ,and then all of us would help you there.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

You may need to reverse the order of the parameters provided:

Exec chngservername 87119, ‘blue’

Or you can provide the parameter names when you call the SP:

Exec chngservername @srvname = ‘blue’ , @keyid = 87119

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @NickCoronado-4758,

Welcome to Microsoft Q&A!

You could refer to Guoxiong's answer.

Create proc chngservername
@keyid int, @srvname nvarchar(120)

If you create the procedure with the parameters in above order, you have to provide the value of the parameters in the same order when you execute the procedure which means that provide the 87119 firstly and ‘blue’ secondly.

Best regards,
Melissa


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.