Dr Amit K Awasthi Dr Amit K Awasthi - 5 months ago 12
SQL Question

SQL server Update Dynamic Column for Some row

I have following Data

----------------------------------------------
Program | GN | GNF | SC |SCF
----------------------------------------------
P01 | 10 | 2 | 5 | 2
P02 | 10 | 2 | 5 | 2


I want:
User execute stored procedure setQuota with parameters @PROGRAM say P02, and some @category say GNF .

Result should be:
Decrease value of Program for that category by one.
that is, for P02 GNF would be 1

I have following Code

DECLARE @SEATCAT NVARCHAR(MAX) = N'GEN'
DECLARE @PROG NVARCHAR(MAX) = N'U03'
declare @sql1 nvarchar(max)

begin
declare @sql nvarchar(max)
set @sql = 'Update SHEAT SET [' + @SEATCAT + '] = [' + @SEATCAT + ']-1 Where PROGRAM='' + P01 +''';
exec sp_executesql @sql
end

Select GEN FROM SHEAT Where PROGRAM=@PROG;`


I am getting errors:

Answer

You are missing a quote and also are using P01 like a variable but it isn't defined. Did you mean to write @PROG so it would be like

Where PROGRAM=''' + @PROG + ''';

As a further note: If either of these variables are coming from user input (eg someone types them in) then you should really look into SQL Injection attacks as you may be allowing arbitrary code to be run through this procedure.