October 13, 2005 at 8:38 am
I would like to implement a single Stored procedure to update or insert to one table. The table has about 20 columns with datatypes of varchar, bit, datatime and int and the primary key is an Identity column. Some of them are NOT NULL columns.
Is there a way to do the Update/Insert without Dynamic SQL. An example will point me to the right direction.
Thanks in advance
October 13, 2005 at 9:14 am
The easiest way I know of is to use flow control:
IF EXIST( SELECT PK FROM MyTable)
UPDATE
ELSE
INSERT
I wasn't born stupid - I had to study.
October 13, 2005 at 9:28 am
Sorry if there wasn't enough information in the original post.
The application calling the SP would know if it was an insert or an update. The update to the table will not be updating all columns. The same would hold true for the insert. The SP would list all columns as parameters but only some columns will be updated. Out of the 20 columns, I may want to update Columns 3,5 and 10 only.
I guess I would have to set default to the input parameters, but can I accomplish that without Dynamic SQL.
October 13, 2005 at 9:58 am
Sounds like an intense CASE statement.
Why do you only want to UPDATE certain fields rather than the entire record? (Obviously, the INSERT will take care of itself...).
I wasn't born stupid - I had to study.
October 14, 2005 at 6:52 am
Since the application will be telling the stored procedure whether it is an insert or an update, that logic is simple to do in the procedure using an IF statement.
For an insert statement also, it is fine - take the values, plug them in since this is insert into...values().
For an update, you can do:
update tablex
set col1 = coalesce(@col1_param, col1),
col2 = coalesce(@col2_parm, col2)...
.
from tablex...
where...
That way, if values are passed in, then those are used...if not, then no harm done and the old values are retained...make sure to pass in NULL to the parameters in that case.
October 14, 2005 at 7:44 am
Thanks. That is actually what I was trying to get across. Why not update the entire table rather than selected fields?
I wasn't born stupid - I had to study.
October 14, 2005 at 10:12 pm
Dear Friend,
Declare One variable querytype varchar(1) Ok. Based one the querytype values you can to insert and update.
For instance
IF querytype='I'
BEGIN
INSERT INTO TABLENAME ( Col1, Col2,......)
Values (@col1,@col2)
END
IF querytype='U'
BEGIN
UPDATE TABLENAME SET (Col1=@col1, Col2=@col2)
END
GO
From that you can do whatever Query to execute based on the Query Type.
I hope this will help to you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply