question

jrobinsontx avatar image
jrobinsontx asked

Building a Dynamic Stored Procedure

I have the following procedure but when trying to execute I get the error below. I can't figure out how to format the string such that it uses the @UserId param. What am I doing wrong?? I know the that the stored proc is getting the vaule inside the @UserId param. I have done a Select @UserId when calling the SP. Msg 245, Level 16, State 1, Procedure sp_GetWellInfo, Line 16 Conversion failed when converting the varchar value 'SELECT Wells.WellId, Wells.API,Wells.WellName, Wells.Operator, Wells.State, Wells.County, Wells.Status, Wells.Formation,convert(varchar,Wells.SpudDate,101) as SpudDate, Wells.StateURL,AssignedWells.BPOWI, AssignedWells.BPONRI, AssignedWells.APOWI, AssignedWells.APONRI FROM Wells INNER JOIN AssignedWells ON Wells.WellId = AssignedWells.WellId WHERE Wells.State = 'TX' and AssignedWells.UserId = ' to data type int. -------------------------------- ALTER PROCEDURE [dbo].[sp_GetWellInfo] ( @County varchar(50) = NULL, @State varchar(50) = NULL, @UserId int ) AS BEGIN Declare @SQLStatement nvarchar(1200) Set @SQLStatement = 'SELECT Wells.WellId, Wells.API, Wells.WellName, Wells.Operator, Wells.State, Wells.County, Wells.Status, Wells.Formation, convert(varchar,Wells.SpudDate,101) as SpudDate, Wells.StateURL, AssignedWells.BPOWI, AssignedWells.BPONRI, AssignedWells.APOWI, AssignedWells.APONRI FROM Wells INNER JOIN AssignedWells ON Wells.WellId = AssignedWells.WellId WHERE Wells.State = ''TX'' and AssignedWells.UserId = ' + @UserId Exec(@SQLStatement) END
stored-proceduresparametersdynamic
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Its because the @UserId is an integer so it is trying to integer add the string to a number, as the integer has a higher datatype precedence. So you would use `+ cast(@Userid as varchar)` However, why is this using dynamic sql? Why not just have the parameters ALTER PROCEDURE [dbo].[sp_GetWellInfo] ( @County varchar(50) = NULL, @State varchar(50) = NULL, @UserId int ) AS BEGIN SELECT Wells.WellId, Wells.API, Wells.WellName, Wells.Operator, Wells.State, Wells.County, Wells.Status, Wells.Formation, convert(varchar,Wells.SpudDate,101) as SpudDate, Wells.StateURL, AssignedWells.BPOWI, AssignedWells.BPONRI, AssignedWells.APOWI, AssignedWells.APONRI FROM Wells INNER JOIN AssignedWells ON Wells.WellId = AssignedWells.WellId WHERE Wells.State = 'TX' and AssignedWells.UserId = @UserId END substituting the other params as necessary
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.