February 3, 2012 at 3:28 am
hi all
i want to write dynamic stored procedure in sql server 2008 for update table
i want use dynamic where clause for limite the update
please help me
declare @where nvarchar(max)
declare @mande bigint
set @mande=0
update balance set
@mande=bal= @mande+(bad-bas)
,tash=(case when bal>0 then 'BD'
when bal<0 then 'BS'
when bal=0 then 'BI'
end )
where id <45
-- @where
February 3, 2012 at 3:35 am
DECLARE @where NVARCHAR(MAX);
DECLARE @SQLStr NVARCHAR(MAX);
SET @where = 'where id <45;'
SET @SQLStr = '
DECLARE @mande bigint;
set @mande=0;
update balance
set @mande=bal= @mande+(bad-bas)
,tash=(case when bal>0 then ''BD''
when bal<0 then ''BS''
when bal=0 then ''BI''
end )
--where id <45
' + @where;
EXEC(@SQLStr);
But I have my questions about the syntactical correctness of your update statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 3, 2012 at 3:38 am
DECLARE @sql NVARCHAR(2000), @Where NVARCHAR(1000), @mande BIGINT
SET @mande=0
SET @sql = 'UPDATE balance' +CHAR(13)+CHAR(10)+
'SET @mande = bal = @innerMande + (bad - bas),' +CHAR(13)+CHAR(10)+
'tash = (CASE WHEN bal > 0' +CHAR(13)+CHAR(10)+
'THEN ''BD''' +CHAR(13)+CHAR(10)+
'WHEN bal < 0' +CHAR(13)+CHAR(10)+
'THEN ''BS''' +CHAR(13)+CHAR(10)+
'WHEN bal = 0' +CHAR(13)+CHAR(10)+
'THEN ''BI'' END)'
SET @Where = 'WHERE id < 45'
IF LEN(@Where) > 0
BEGIN
SET @sql = @sql + CHAR(13) + CHAR(10) + @Where
EXEC sp_executesql @sql, N'@innerMande BIGINT', @innerMande = @mande
END
ELSE
BEGIN
PRINT 'ERROR!! NO WHERE CLAUSE FOR UPDATE: ' + CHAR(13) + CHAR(10) + @sql
END
February 3, 2012 at 6:19 am
thank you very much
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply