Execute StoredProcedure by passing parameter value same as column name in SQL Server

nagaraju...

SOLVED CLOSED ACTIVE ANSWERED
User: nagaraju60
Posted: on Sep 02, 2016 04:30 AM
Forum: SQL Server
Answer: 2
Views: 5466

in my sql database table 'dbo.TABLE1' i have column name are 1,2,3,4,5 as per my requirement. if i set column names as numeric its automatically set as [1],[2],[3],[4],[5].

all columns are nvarchar(5)

below is my table structure

ROW_NAME|[1]|[2]|[3]|[4]|[5]| --->

Column names (Row name, [1],[2],[3],[4],[5])

A | 1 | 2 | 3 | 4 | 5 | ---> Values

B | 0 | 2 | 0 | 4 | 5 | ---> Values

C | 1 | 0 | 0 | 0 | 5 | ---> Values

please observe column name and values are same except Row_Name.

by passing parameter values from frontend (asp.net) and using stored procedure i am updating my 'dbo.TABLE1'.

below is my stored procedure

---------------------------

CREATE PROCEDURE [dbo].[SP_UPDATE]
-- Add the parameters for the stored procedure here
(

   @Row nvarchar(1), -- Here i Pass parameter value 'B'
   @Column nvarchar(1), -- Here i pass parameter value '3'
   @Status char -- Here i pass parameter value 'R'
)
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF(@Status='R')
BEGIN

UPDATE dbo.TABLE1 SET [@Column]=@Column WHERE Row_Name=@Row

END

ELSE IF(@Status='S')
BEGIN

UPDATE dbo.TABLE1 SET [@Column]=@Column WHERE Row_Name=@Row

END
END

i want out put as,

ROW_NAME|[1]|[2]|[3]|[4]|[5]| --->

Column names (Row name, [1],[2],[3],[4],[5])

A | 1 | 2 | 3 | 4 | 5 | ---> Values

B | 0 | 2 | 3 | 4 | 5 | ---> Values

C | 1 | 0 | 0 | 0 | 5 | ---> Values

if i pass @Column value 1 , @Row value B Then,

ROW_NAME|[1]|[2]|[3]|[4]|[5]| --->

Column names (Row name, [1],[2],[3],[4],[5])

A | 1 | 2 | 3 | 4 | 5 | ---> Values

B | 1 | 2 | 0 | 4 | 5 | ---> Values

C | 1 | 0 | 0 | 0 | 5 | ---> Values

i am getting error at [@Column]..

How to solve it

AnandM

0 5

Reply
User: AnandM
Replied: on Sep 02, 2016 05:32 AM
Report

Hi nagaraju,

Share your table data and script of table creation.

nagaraju...

0 6

Reply
User: nagaraju60
Replied: on Sep 02, 2016 09:42 PM
Report

AnandM says:
Hi nagaraju,

Share your table data and script of table creation.

CREATE TABLE [dbo].[TABLE1](	
	[Row_Name] [char](1) NOT NULL,
	[1] [varchar](5) NULL,
	[2] [varchar](5) NULL,
	[3] [varchar](5) NULL,
	[4] [varchar](5) NULL,
	[5] [varchar](5) NULL	
)

ROW_NAME|[1]|[2]|[3]|[4]|[5]|
     A  | 1 | 2 | 3 | 4 | 5 |
     B  | 0 | 2 | 0 | 4 | 5 |
     C  | 1 | 0 | 0 | 0 | 5 |

AnandM

0 7

Reply
ANSWER
User: AnandM
Replied: on Sep 06, 2016 09:55 AM
Report

Hi nagaraju,

Refer below query

SQL

DECLARE @column_name nvarchar(50) 
SET @column_name = '2'
DECLARE @Row varchar(30) 
SET @Row = 'C'
SELECT @Row,@column_name
DECLARE @sql NVARCHAR(MAX)
DECLARE @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@Row varchar(30),@column_name nvarchar(50)';
SET @sql = N'update dbo.TABLE1 SET ['+ @column_name+ '] = @column_name WHERE Row_Name = @Row;'
EXEC sp_executesql @sql, @ParmDefinition, @Row,@column_name;

You need to add above code in your SP and modify according to your requirement.

Hope this works for you.

nagaraju...

0 8

Reply
ANSWER
User: nagaraju60
Replied: on Sep 08, 2016 05:02 AM
Report

AnandM says:
Hi nagaraju,

Refer below query

SQL

1

2

3

4

5

6

7

8

9

10

DECLARE @column_name nvarchar(50)

SET @column_name = '2'

DECLARE @Row varchar(30)

SET @Row = 'C'

SELECT @Row,@column_name

DECLARE @sql NVARCHAR(MAX)

DECLARE @ParmDefinition nvarchar(500)

SET @ParmDefinition = N'@Row varchar(30),@column_name nvarchar(50)';

SET @sql = N'update dbo.TABLE1 SET ['+ @column_name+ '] = @column_name WHERE Row_Name = @Row;'

EXEC sp_executesql @sql, @ParmDefinition, @Row,@column_name;

You need to add above code in your SP and modify according to your requirement.

Hope this works for you.

after changing above query.. it is executed.

DECLARE @column_name nvarchar(50)
SET @column_name = '2'
DECLARE @Row varchar(30)
SET @Row = 'C'
DECLARE @sql NVARCHAR(MAX)

SET @sql = N'update dbo.TABLE1 SET ['+@column_name+'] ='+ @column_name+' WHERE Row_Name ='''+@Row+''
EXEC sp_executesql @sql

THANKS

Execute StoredProcedure by passing parameter value same as column name in SQL Server

Company

Explore

Follow us

Contact Us