CODEWITHSUNDEEP
sql-serverstored-procedures
veix
veix

Reputation: 39

Inserting stored procedure parameter to table only inserts first letter

I have stored procedure where I have parameter with datatype sql_variant. This parameter is then converted and inserted into parameter that is nvarchar(MAX) datatype. Inserting dates and floats are working fine. Then as example inserting into varchar(60) cell doesn't seem to work and only inserts first letter. When I add SELECT statements for the parameters in stored procedure it shows after executing the information to be inserted correctly and it only fails the actual insertion to table.

How to insert whole nvarchar to varchar(60) or similar cell?

Here are important parts of the code without too much extra:

CREATE PROCEDURE proc_name 
                 @param1 nvarchar(30), 
                 @param2 nvarchar(30), 
                 @param3 sql_variant
AS
BEGIN
SET NOCOUNT ON;

DECLARE @update_param nvarchar(MAX);
SET @update_param = CONVERT(nvarchar(MAX), @param3);

-- Lots of not important stuff here such as getting datatype from INFORMATION_SCHEMA

DECLARE @Sql nvarchar(MAX);
SET @Sql = N' DECLARE @variable ' + QUOTENAME(@datatype) + N' = @update_param '
         + N' UPDATE table_name'
         + N' SET ' + @param1 + N' = @variable '
         + N' WHERE something = ' + @param2    

Exec sp_executesql @Sql, N'@update_param nvarchar(MAX)', @update_param

Adding SELECT @Sql to the procedure gives following result: 

DECLARE @variable [varchar] = @update_param  
UPDATE table_name 
SET column_name = @variable 
WHERE something = thingsome

When @param1 = column_name, @param2 = thingsome

Edit: I read multiple questions on this topic and they all told to declare nvarchar length. Here I have it declared as nvarchar(MAX).

Edit2: Added code bits.

Edit3: After adding code and help in comments the answer is that there is length undeclared for @datatype in @Sql

Upvotes: 1

Views: 368

Answers (1)

Thom A
Thom A

Reputation: 95571

This doesn't answer the question at hand, however, the SP you have is open to injection. Raw string concatenation like that is a dangerous game to play. This is far safer:

CREATE PROCEDURE proc_name 
                 @param1 nvarchar(30), 
                 @param2 nvarchar(30), 
                 @param3 sql_variant
AS
BEGIN
SET NOCOUNT ON;

DECLARE @update_param nvarchar(MAX);
SET @update_param = CONVERT(nvarchar(MAX), @param3);

-- Lots of not important stuff here such as getting datatype from INFORMATION_SCHEMA

DECLARE @Sql nvarchar(MAX);
SET @Sql = N' DECLARE @variable ' + QUOTENAME(@datatype) + N' = @dupdate_param' --Where is the value of @datatype coming from?
         + N' UPDATE table_name'
         + N' SET ' + QUOTENAME(@param1) + N' = @variable '
         + N' WHERE something = @dparam2;'

Exec sp_executesql @Sql, N'@dupdate_param nvarchar(MAX), @dparam2 nvarchar(30)',@dupdate_param = @update_param, @dparam = @param2;

GO

Upvotes: 1

Related Questions