I am creating a procedure where I need to check whether a column is present in the database table. If it is not present then add the column and if present then update that column with a value for a particular id. I am pasting my code below: The problem is
when I try using this procedure it adds the column perfectly but was not able to update the table.
One more query, currently my resid i.e. where clause is varchar but if i change it to int. It gives me an error that can not convert varchar into data type int.
Please clarify me this point why if in @tsql1 I am concatenating the integer Respid with the string, it is throwing me this error.
abhinavsaxen...
Member
48 Points
41 Posts
SQL Procedure
Feb 24, 2012 06:55 AM|LINK
Hi All,
I am creating a procedure where I need to check whether a column is present in the database table. If it is not present then add the column and if present then update that column with a value for a particular id. I am pasting my code below: The problem is when I try using this procedure it adds the column perfectly but was not able to update the table.
USE
[Dummy]
GO
/****** Object: StoredProcedure [dbo].[usp_Sadd] Script Date: 02/24/2012 12:02:43 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
--usp_Sadd 'abhinav2','01','1001'
ALTER
PROCEDURE [dbo].[usp_Sadd]
@SName varchar(100),
@Sval varchar(100),
@RespId varchar(100)
AS BEGIN
declare @SName1 varchar(100)
declare @tsql varchar(200)
declare @tsql1 varchar(250)
declare @aa varchar(10)
begin tran
select @SName1=@SName
set @tsql = 'alter table SessionAdd add [' + @SName1 + '] varchar(100)'
set @tsql1 = 'update SessionAdd set [' + @SName1 + '] = ["'+ @Sval +'"] where RespId= "' + @RespId + '"'
SET NOCOUNT on;
if COL_LENGTH('SessionAdd',@SName1) is null
begin exec(@tsql) end
exec(@tsql1)
commit tran
END
Kindly help me out in this as I have already wasted 2 days for this.
santosh.jagd...
Star
7625 Points
1454 Posts
Re: SQL Procedure
Feb 24, 2012 07:01 AM|LINK
if column is alredy exist then is it updating the value?
I can't see the code in your stored procedure, to check whether column is exist.
MCP
abhinavsaxen...
Member
48 Points
41 Posts
Re: SQL Procedure
Feb 24, 2012 07:29 AM|LINK
if COL_LENGTH('SessionAdd',@SName1) is null
is the code whether the column is present or not
sujithkumar
Contributor
3744 Points
739 Posts
Re: SQL Procedure
Feb 24, 2012 07:42 AM|LINK
HI,
I HAVE CREATED A TEMPORARY PROCEDUTE AND TESTED WITH UR CODE,
I MADE SOME CHANGES IN YOUR CODE
PLEASE GO THROUGH IT...
U HAVE USED ( " ) QUATION MARK I NYOUR CODE... YOU SHOULD NOT USE THAT .. YOU SHOUD USE SINGLE QOUTE (').
PLEASE CHANGE THE TABLE NAMES AS U REQUIRED FROM FOLLOWING STORED PROCEDUE.. I TESTED IT.. ITS WORKING FINE....
--#usp_Sadd 'abhinav2','01','1001'
--#usp_Sadd 'VVVV','01','1001'
--create table TEST1(RespId VARCHAR(100))
--DROP TABLE TEST1
--SELECT * FROM TEST1
alter PROCEDURE #usp_Sadd
@SName varchar(100),
@Sval varchar(100),
@RespId varchar(100)
AS BEGIN
declare @SName1 varchar(100)
declare @tsql varchar(200)
declare @tsql1 varchar(250)
declare @aa varchar(10)
--begin tran
select @SName1=@SName
set @tsql = 'alter table TEST1 add [' + @SName1 + '] varchar(100)'
set @tsql1 = 'update TEST1 set [' + @SName1 + '] = '''+ @Sval +''' where RespId= ''' + @RespId + ''''
PRINT @tsql
PRINT @tsql1
--RETURN
SET NOCOUNT on;
if COL_LENGTH('TEST1',@SName1) is null
begin
PRINT 'ALTERED'
exec(@tsql)
end
ELSE
BEGIN
PRINT 'UPDATED'
END
exec(@tsql1)
--commit tran
END
abhinavsaxen...
Member
48 Points
41 Posts
Re: SQL Procedure
Feb 24, 2012 07:57 AM|LINK
Thanks Sujith !!
One more query, currently my resid i.e. where clause is varchar but if i change it to int. It gives me an error that can not convert varchar into data type int.
Please clarify me this point why if in @tsql1 I am concatenating the integer Respid with the string, it is throwing me this error.
sujithkumar
Contributor
3744 Points
739 Posts
Re: SQL Procedure
Feb 24, 2012 08:16 AM|LINK
hi,
are you changing stored procedure parameter as int (or) table cloumn type as INT
if ur changing parameter when u pass value to Stored procedure remove single qoute...
abhinavsaxen...
Member
48 Points
41 Posts
Re: SQL Procedure
Feb 24, 2012 08:48 AM|LINK
I am changing the stored procedure parameter as int and table column as int
So, in this case set @tsql1 = 'update TEST1 set [' + @SName1 + '] = '''+ @Sval +''' where RespId= ''' + @RespId + ''''
changes to
set @tsql1 = 'update TEST1 set [' + @SName1 + '] = '''+ @Sval +''' where RespId= ' + @RespId + ''
is this right?
srinanthuram
Contributor
6800 Points
1549 Posts
Re: SQL Procedure
Feb 24, 2012 09:06 AM|LINK
hi
change it
set @tsql1 = "update TEST1 set [ ColoumnName ] = @Sval where RespId= @RespId "
thank u
sujithkumar
Contributor
3744 Points
739 Posts
Re: SQL Procedure
Feb 24, 2012 09:15 AM|LINK
hi,
try this
--#usp_Sadd 'abhinav2','01',1001
--#usp_Sadd 'VVVV','01','1001'
--create table TEST1(RespId int)
--DROP TABLE TEST1
--SELECT * FROM TEST1
CREATE PROCEDURE #usp_Sadd
@SName varchar(100),
@Sval varchar(100),
@RespId int
AS BEGIN
declare @SName1 varchar(100)
declare @tsql nvarchar(2000)
declare @tsql1 nvarchar(2000)
declare @aa varchar(10)
--begin tran
select @SName1=@SName
set @tsql = 'alter table TEST1 add [' + @SName1 + '] varchar(100)'
set @tsql1 = 'update TEST1 set [' + @SName1 + '] = '''+ @Sval +''' where RespId= ' + cast( @RespId as varchar(100)) + ''
PRINT @tsql
PRINT @tsql1
--RETURN
SET NOCOUNT on;
if COL_LENGTH('TEST1',@SName1) is null
begin
PRINT 'ALTERED'
exec(@tsql)
end
ELSE
BEGIN
PRINT 'UPDATED'
END
exec(@tsql1)
--commit tran
END
i have tested.. its working fine......
hope this will help you.. plz change table and column names as per ur table name...
abhinavsaxen...
Member
48 Points
41 Posts
Re: SQL Procedure
Feb 25, 2012 12:05 PM|LINK
Awesome......................... Very Thank You !!!!