ASP.NET Stored PRocedures

Question

1.00/5 (1 vote)

See more:

SQL

Alter procedure VisitorDetails1
@p_name nvarchar(50),
@p_City nvarchar(100),
@p_Dept nvarchar(max),
@p_TableName nvarchar(50)
as

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@p_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 '+@p_TableName+'(Name,City,Dept) values(@p_name,@p_City,@p_Dept)'

--set @set='insert into '+@TableName+' values(@name,@City,@Dept)'

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

--Parameters Execution Part:
exec VisitorDetails1 Perumal,Chennai,IT,VSampleData--(This Is Table Name I created)

After Executing Result IS:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@p_".
Success..

Actually I need to Check the table is already exist or not.. if exist in that same table the data should be inserted other wise it should execute Table is Not There. These all should dynamically..

Please Help Me what I need to Do..
Thanks Advace,.

Posted 7-Jan-13 1:49am

Perumalkutti

Updated 7-Jan-13 2:01am

Manas Bhardwaj

v2

Add a Solution

Comments

Aarti Meswania 7-Jan-13 7:57am

do not repost questions
http://www.codeproject.com/Answers/523251/ASP-NETplusSqlplusServerplusInplusStoredplusProced#answer3
use solution-3 it will work

your store procedure is correct just modify Query as I have mentioned
you need not to change parameter @name to @P_name
It never metters

2 solutions

Solution 2

I guess the statement should be:

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

Posted 7-Jan-13 2:05am

Manas Bhardwaj

Updated 7-Jan-13 2:06am

v2

Comments

__TR__ 7-Jan-13 8:18am

+5.
Congratulations for winning MVP 2013:)

Manas Bhardwaj 7-Jan-13 8:29am

thx :)

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)

Zafar Sultan · Accepted Answer · 2013-01-07T02:03:00

Firs, you are reposting your question. The solution to one of your problem is Here[^]. You should improve your question if there is additional doubt or clarification. Second, the flow of your stored procedure should go like this:

SQL

Alter procedure VisitorDetails1
@p_name nvarchar(50),
@p_City nvarchar(100),
@p_Dept nvarchar(max),
@p_TableName nvarchar(50)
as
BEGIN
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@p_TableName)) 
BEGIN
--Declare @set nvarchar(50)
Declare @set nvarchar(500)
--Replace your query to 
set @set='insert into '+@TableName+'(Name,City,Dept) values(''' + @name + ''',''' + @City + ''',''' + @Dept + ''')'
Print 'Success'
END
else
BEGIN
Print 'Table is Not there'
END
END