I am attempting to make a stored procedure work by triggering the SP through a batch file using SQLCMD, with user-defined variables. I can already confirm my stored procedure works, because I tested it this way using hard-coded variables. For reference, this is the batch file code:
SET /P Param1 = Enter job number here:
SET /P Param2 = Enter number for current status here:
sqlcmd -S DevTest -d test_database -Q "EXEC dbo.JStatUpd @jobNum = Param1, @stat = Param2"
PAUSE
(EDIT: I have left the sqlcmd line as-is for ease of reference.)
My difficulty lies in working with user-defined variables. Specifically, I'm having an issue converting the user-defined variables--both of my variables should be integers.
I recognize that all of the SET variables automatically come in as nvarchar, but I haven't been able to find a way to convert them to int; I've gotten errors for each attempt I've made at using CAST or CONVERT.
My question is: Is it possible to to convert variables using CAST or CONVERT inside the SQLCMD in-line query? Or am I going about this the wrong way when I ought to be using a different method?
@Nishad had the right idea, but it turns out that something else needed fixing as well. For the sake of being thorough, here's the final batch file code I wound up using:
SET /P Param1 = Enter job number here:
SET /P Param2 = Enter current status here:
sqlcmd -S DevTest -d test_database -Q "EXEC dbo.JStatUpd @jobNum=N'%Param1%', @stat=%Param2%"
PAUSE
It was only with this specific formatting that I could actually get my user-entered values to work properly; @echo showed me that I was getting blanks no matter what I entered, so I started playing with the query parameter formats and found that this worked.