Structure of a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Structure of a stored procedure

Author

Topic

er.bhupinder53
Starting Member

13 Posts

Posted - 2011-07-14 : 14:34:58

Hi guys, Im updating a table named my_table which has columns : colA, ColB, ColC
I have created a stored procedure as follows: Please correct the errors:

update my_table
set colA = case
when 'Value1' then (update my_table
set colB = NewcolB
From
( select colB, newcolB = case colA
when 'Value1' then 'PV0'
end
+ right('000' + convert(varchar(15), row_number() over(partition by ColA order by ColA) + 99 ), 15)
from my_table)
my_table
)

ELSE 'ABC'
END

Now, If I run just inner part, it updates my_table but I want to put a condition if colA = 'Value1' then it should update my_table else it should update with the other value, that will be dealt later.

Thanks !!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-15 : 15:27:02

>> I am updating a table named my_table which has columns : cola, colb, colc
I have created a stored procedure as follows: Please correct the errors: <<

DDL, please.

Your whole approach is what is wrong. SQL is a declarative language, and CASE is an expression and not a statement. Your code looks like you chopped up a printout and pasted it back together at random. What are you trying to do? I cannot figured it out and I have written eight books on SQL!

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources