Click here to Skip to main content
12,448,348 members (54,216 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL-Server
Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
as
 
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName)) 
--IF db_id('@TabName') IS NOT NULL
--IF OBJECT_ID('@TabName','U') is not null
begin
 
Declare @set nvarchar(50)
set @set='insert into '+@TableName+'(Name,City,Dept) values(@name,@City,@Dept)'
 
--set @set='insert into '+@TableName+' values(@name,@City,@Dept)'

exec(@set)
Print 'Success'
end
else
begin
print 'Table is Not there'
end

My Result IS After Exectuing:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@name".
Success..


Please Help Me What I need to Do..
Advance Thanks.
Posted 7-Jan-13 1:30am
Updated 7-Jan-13 1:32am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
as
 
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName)) 
--IF db_id('@TabName') IS NOT NULL
--IF OBJECT_ID('@TabName','U') is not null
begin
 
Declare @set nvarchar(50)
set @set='insert into '+@TableName+'(Name,City,Dept) values(''' + @name + ''',''' + @City + ''',''' + @Dept + ''')'
 
exec(@set)
Print 'Success'
end
else
begin
print 'Table is Not there'
end
Happy Coding!
Smile | :)
  Permalink  
v3
Comments
Zafar Sultan 7-Jan-13 7:43am
   
+5
Aarti Meswania 7-Jan-13 7:44am
   
thank you! :)
__TR__ 7-Jan-13 8:16am
   
5ed!
Aarti Meswania 7-Jan-13 8:17am
   
Thank you!
:)
URVISHSUTHAR 7-Jan-13 11:08am
   
Easy to pass "SQL Injection" on this prod. So think about it :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

This will not work
Exec statement execute in different context where the variables are not available.

instead concatenate the values to the string and exectuee

for exgs

Declare @str nVarchar(50)

set @str ='Insert into Tb1 values(' + @r + ')'
exec(@str)
  Permalink  
Comments
Perumalkutti 7-Jan-13 8:10am
   
I Tried This also... But Did't Work Sir...
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Change this
set @set='insert into '+@TableName+'(Name,City,Dept) values(@name,@City,@Dept)'
to
set @set='insert into '+@TableName+'(Name,City,Dept) values('+@name+','+@City+','+@Dept+')'

Change the above sql statement as per your requirement. You will need to wrap your string in apostrophes.
  Permalink  
Comments
Perumalkutti 7-Jan-13 8:09am
   
I Tried This Also sir.. But didt work
Zafar Sultan 7-Jan-13 8:16am
   
You are jumping from one thread to another. Stick to one. The complete solution to your problem is posted here: http://www.codeproject.com/Questions/523255/ASP-NETplusStoredplusPRocedures Now do not keep updating both the threads. Use one only. Please.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hi,
change your variable name
@name to @p_name

do this to all......
it will definitely work..
  Permalink  
v2
Comments
Perumalkutti 7-Jan-13 8:09am
   
the same problem sir..
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

You have to make changes at 2 places. Highlighted in bright colors

one is change the size of @set variable.

When you declare @set=50, it can hold only 50 characters and if you try to insert it will fail


Alter procedure VisitorDetails1
@name nvarchar(50),
@City nvarchar(100),
@Dept nvarchar(max),
@TableName nvarchar(50)
as

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName))
--IF db_id('@TabName') IS NOT NULL
--IF OBJECT_ID('@TabName','U') is not null
begin

Declare @set nvarchar(500)
set @set='insert into '+@TableName+'(Name,City,Dept) values(''' + @name + ''',''' + @City + ''',''' + @Dept + ''')'
print @set
exec(@set)
Print 'Success'
end
else
begin
print 'Table is Not there'
end



You have to make changes at 2 places
  Permalink  
Comments
Perumalkutti 8-Jan-13 7:53am
   
Ya I Got this after tried a lot.. But Good Solution for this Thanks a lot Sir..

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160811.3 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100