how to pass tablename,column name and value as paramerter in store procedure ..facing some problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

SQL

USE [customcontrolsetting]
GO
/****** Object:  StoredProcedure [dbo].[sp_all_task]    Script Date: 10/10/2013 12:50:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_all_task] 

	@table_name nvarchar(max),
    @taction nvarchar(max),
    @tcolumn nvarchar(max),
    @tvalues nvarchar(max),
    @tconditions nvarchar(max)

	
AS
BEGIN
SET NOCOUNT ON;

DECLARE @cmd AS NVARCHAR(max)
 
if(@taction = 'insert')

SET @cmd='insert into '+@table_name+' ('+@tcolumn+') values('+@tvalues+') '
EXEC sp_all_task @cmd 

END

this is my query in store procedure ...

EXEC [sp_all_task] @table_name='AA',@taction='insert', @tcolumn='CC',@tvalues='DD',@tconditions='EE'

when i execute this folowing error is occured

error

ASM

Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 201, Level 16, State 4, Procedure sp_all_task, Line 0
Procedure or function 'sp_all_task' expects parameter '@taction', which was not supplied.
Msg 217, Level 16, State 1, Procedure sp_all_task, Line 20
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Posted 9-Oct-13 23:15pm

MAYANK GEETE

Updated 9-Oct-13 23:39pm

Zafar Sultan

v2

Add a Solution

4 solutions

Solution 4

EXEC sp_all_task 'c','d','e','f','b'

Posted 10-Oct-13 0:29am

Siva Hyderabad

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-10-09T23:32:00

Solution 1

Try

SQL

EXEC [sp_all_task] 'AA','insert','CC','DD','EE'

Also, just write

SQL

EXECUTE (@cmd)

instead of

SQL

EXEC sp_all_task @cmd

in your SP.

Posted 9-Oct-13 23:32pm

Zafar Sultan

Updated 9-Oct-13 23:37pm

v2

Comments

MAYANK GEETE 10-Oct-13 7:51am

THANKS ZAFAR ..IT'S WORKING THANK YOU THANK YOU SO MUCH

Zafar Sultan 10-Oct-13 8:11am

Welcome.

Tausif/Pintu · Accepted Answer · 2013-10-10T00:00:00

Solution 2

Changed your string to

SQL

SET @cmd='insert into '+@table_name+' ('+@tcolumn+') values('+Char(39)+@tvalues+Char(39)+')'

And
Just write

SQL

EXEC @cmd

for executing string.
Here Char(39) is ASCII value for single quote.

Posted 10-Oct-13 0:00am

Tausif/Pintu

Updated 10-Oct-13 0:24am

v2

Pravinjas · Accepted Answer · 2013-10-10T00:07:00

Solution 3

Use the

EXECUTE (@cmd)

instead of

EXEC sp_all_task @cmd

Posted 10-Oct-13 0:07am

Pravinjas