Insert values using stored procedure with dynamic table name

Question

I have this SQL Stored procedure (i'm modifying it though)

ALTER Procedure [dbo].[spUpdateProfile] 
(
@TableName nvarchar(30),
@First_Name nvarchar(max),
@Middle_Name nvarchar(max),
@Last_Name nvarchar(max),
@Birthday date,
@Address nvarchar(max),
@Websites nvarchar(max),
@Email nvarchar(max),
@Contact_Number nvarchar(max),
@Gender nvarchar(max))
AS

DECLARE @SQLString NVARCHAR(MAX)
SET @SQLString = 'TRUNCATE TABLE ' + @TableName                       
EXEC (@SQLString)

DECLARE @SQLString1 NVARCHAR(MAX)
SET @SQLString1= 'INSERT INTO [dbo].[' + @TableName +
'](First_Name,
Middle_Name,
Last_Name,
Birthday,
Address,
Websites,
Email,
Contact_Number,
Gender) VALUES(' + @First_Name + ','
+@Middle_Name + ','
+@Last_Name + ','
+convert(nvarchar(max),@Birthday) + ','
+@Address + ','
+@Websites + ','
+@Email + ','
+@Contact_Number +','
+@Gender +')'
print @SQLString1
EXEC (@SQLString1)

Then when I tried putting values...

USE [PersonalOrganizerDB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[spUpdateProfile]
        @TableName = N'User2',
        @First_Name = N'Caress',
        @Middle_Name = N'caress',
        @Last_Name = N'Caress',
        @Birthday = N'02/06/1992',
        @Address = N'asf',
        @Websites = N'asd',
        @Email = N'a@dfsgsd',
        @Contact_Number = N'42',
        @Gender = N'Female'

SELECT  'Return Value' = @return_value

GO

I get these errors

Msg 207, Level 16, State 1, Line 1 Invalid column name 'Caress'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'caress'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Caress'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'asf'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'asd'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'a@dfsgsd'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Female'.

When I print my SQLstring, here it is...

INSERT INTO [dbo].[User2](First_Name,
Middle_Name,
Last_Name,
Birthday,
Address,
Websites,
Email,
Contact_Number,
Gender) VALUES(Caress,caress,Caress,1992-02-06,asf,asd,a@dfsgsd,42,Female)

By the way, I am using the stored procedure on a C# web program. I am seeing the same error message when I run my website. I tried inputting values using "Execute stored procedure on MSSQL management server and C# program (through parameters.add....) please help. thanks!

I think there are comma's missing in query for string values. — Shahid Iqbal
– Shahid Iqbal, Commented Nov 27, 2013 at 4:21
@ShahidIqbal where is that part? Kindly tell me where it is. You may see the content of SQLString1 there. I used the "print SQLString1" command to verify. I see that there is no comma missing. Please let me know if i miss one — Caress Castañares
– Caress Castañares, Commented Nov 27, 2013 at 4:25

Cris · Accepted Answer · 2013-11-27 04:26:04Z

1

try

SET @SQLString1= 'INSERT INTO [dbo].[' + @TableName +
    '](First_Name,
    Middle_Name,
    Last_Name,
    Birthday,
    Address,
    Websites,
    Email,
    Contact_Number,
    Gender) VALUES(''' + @First_Name + ''','''
    +@Middle_Name + ''','''
    +@Last_Name + ''','''
    +convert(nvarchar(max),@Birthday) + ''','''
    +@Address + ''','''
    +@Websites + ''','''
    +@Email + ''','''
    +@Contact_Number +''','''
    +@Gender +''')'
    print @SQLString1
    EXEC (@SQLString1)

answered Nov 27, 2013 at 4:26

Cris

13.4k5 gold badges37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Caress Castañares Over a year ago

That is great! I am so very grateful :D hahahaa! thank you very much!

Collectives™ on Stack Overflow

Insert values using stored procedure with dynamic table name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related