How to write a dynamic query for insert statement in sql server ?

Question

0.00/5 (No votes)

See more:

, +

I want a dynamic insert query for insert data in table, I have 5 tables which have same column want a single query to insert data into tables.

I create sp

SQL

create proc insertdata
(
@tableName nvarchar(50),
@name nvarchar(50),
@Nrange nvarchar(50),
@isActive bit
)
as 
begin

declare @sql nvarchar(200)
set @sql = 'isert into '+@tableName +' values('+@name +','+@Nrange +','+@isActive +')';
exec(@sql)
end

like this but when try to execute it throws error

ERROR
------------

C#

Msg 402, Level 16, State 1, Procedure insertdata, Line 15
The data types nvarchar and bit are incompatible in the add operator.

Posted 6-Jan-16 0:07am

Dj@y

Updated 25-Feb-16 8:57am

Add a Solution

Comments

aarif moh shaikh 6-Jan-16 6:12am

check your insert spelling.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*dan!sh* · Answer 1 · 2016-01-06T00:19:00

Solution 1

Your parameter @isActive is of type bit while rest of the command is nvarchar. You cannot use + operator between these two data types. You might want to convert the bit parameter to nvarchar to get this working.

Posted 6-Jan-16 0:19am

dan!sh

Comments

Dj@y 6-Jan-16 7:26am

after change data type sp is created
But afetr exicution of sp got error
error-----
Msg 2812, Level 16, State 62, Procedure insertdata, Line 17
Could not find stored procedure 'insert into tbltest2(Name,Nrange,isActive) values(test,3 - 5,1)'

Richard Deeming · Answer 2 · 2016-02-25T08:57:00

A perfect example of why stored procedures don't automatically protect you from SQL Injection[^]!

Use sp_executesql[^] to execute your query, passing the parameters as parameters. You also need to validate that the table name is a valid table in the current database.

SQL

CREATE PROC insertdata
(
    @tableName nvarchar(50),
    @name nvarchar(50),
    @Nrange nvarchar(50),
    @isActive bit
)
As 
BEGIN
DECLARE @sql nvarchar(max);
DECLARE @RealTableName sysname;
    
    SET NOCOUNT ON;
    
    SELECT
        @RealTableName = QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    FROM
        sys.tables As T
        INNER JOIN sys.schemas As S
        ON S.schema_id = T.schema_id
    WHERE
        T.name = @tableName
    ;
    
    If @@ROWCOUNT = 0 RAISERROR('Invalid table: "%s"', 16, 1, @tableName);

    SET @sql = N'INSERT INTO ' + @RealTableName + N' VALUES (@name, @Nrange, @isActive)';
    
    EXEC sp_executesql @sql, 
        N'@name nvarchar(50), @Nrange nvarchar(50), @isActive bit',
        @name = @name,
        @Nrange = @Nrange,
        @isActive = @isActive
    ;
END

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]