CODEWITHSUNDEEP
sqlstored-proceduresparameterssql-server-2012
Cliff Crerar
Cliff Crerar

Reputation: 443

Insert procedure with 2 string parameters

I wrote this procedure to insert 2 string variables into a table:

ALTER Procedure 
[dbo].[IND] (@VAssetID varchar(50), @UAssetID nvarchar(255))
As
BEGIN
  Declare @Query1  as varchar(max)
  Set @Query1 = 
  'Insert into IndMatch(V_AssetID,U_AssetID) values('+ @VAssetID +','+@UAssetID+')'
  EXECUTE(@Query1)
END

When I run the procedure with number strings only it works fine as soon as I try and insert alphanumeric codes the procedure fails.

When the procedure executes with numbers strings only:

Exec IND @VAssetID = '231243332', @UAssetID = '21343321'

The procedure executes fine with and inserts the values into the table.

When the procedure executes with alphanumeric strings:

Exec IND @VAssetID = '2312I43332', @UAssetID = '21T343R321'

It generates the error:

Incorrect syntax near 'I43332'.

Please assist

Upvotes: 0

Views: 377

Answers (2)

Gareth Lyons
Gareth Lyons

Reputation: 1972

Why execute as a string? This'll work just fine:

      ALTER Procedure [dbo].[IND] (@VAssetID varchar(50), @UAssetID nvarchar(255))
      As
      BEGIN
      Insert into IndMatch(V_AssetID,U_AssetID) values(@VAssetID, @UAssetID)
      END

Upvotes: 3

Fuzzy
Fuzzy

Reputation: 3810

you are missing the [ ' ] around the values.

It will try to execute an insert like so:

Insert into IndMatch(V_AssetID,U_AssetID) values(2312I43332,21T343R321 )

but the correct syntax should be 

Insert into IndMatch(V_AssetID,U_AssetID) values('2312I43332','21T343R321' )

you need to do this:

ALTER Procedure 
[dbo].[IND] (@VAssetID varchar(50), @UAssetID nvarchar(255))
As
BEGIN
  Declare @Query1  as varchar(max)
  Set @Query1 = 
  'Insert into IndMatch(V_AssetID,U_AssetID) values('''+ @VAssetID +''','''+@UAssetID+''')'
  EXECUTE(@Query1)
END

Run this and this should explain it:

DECLARE @Query1 VARCHAR(MAX), @VAssetID VARCHAR(50), @UAssetID NVARCHAR(255);

SET @VAssetID = '231243332';

SET @UAssetID = '21343321';

SET @Query1 = 'Insert into IndMatch(V_AssetID,U_AssetID) values('+@VAssetID+','+@UAssetID+')';

PRINT @Query1;

SET @Query1 = 'Insert into IndMatch(V_AssetID,U_AssetID) values('''+@VAssetID+''','''+@UAssetID+''')';

PRINT @Query1;

PRINT RESULT:

enter image description here

Better yet you can do this as I don't see a need for dynamic sql here:

ALTER Procedure 
[dbo].[IND] (@VAssetID varchar(50), @UAssetID nvarchar(255))
As
BEGIN


   Insert into IndMatch(V_AssetID,U_AssetID) values(  @VAssetID  , @UAssetID ) 

END

Upvotes: 1

Related Questions