Or:
IF NOT EXISTS (
SELECT tablePK
FROM mytable
WHERE pk_id = @pk_id -- replace pk_id with whatever your identity
)
BEGIN
--Do my insert stuff here
END
ELSE
BEGIN
--Do my update stuff here
END
On Tue, Apr 20, 2010 at 12:05 PM, Carl Von Stetten
<[email protected]>wrote:
>
> Michael,
>
> Try something like this
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE PROCEDURE test_sp
> -- Add the parameters for the stored procedure here
> @pk_id INT, -- I assume the table in question as an integer
> identity column
> @rows INT OUTPUT
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> -- Insert statements for procedure here
> SELECT @rows = count(*)
> FROM my_table
> WHERE pk_id = @pk_id -- replace pk_id with whatever your identity
> column is named
>
> IF @rows > 0
> --Do my update stuff here
> ELSE
> --Do my insert stuff here
>
> END
> GO
>
> HTH,
> Carl
>
> On 4/20/2010 9:29 AM, Michael Dinowitz wrote:
> > Hi all,
> >
> > I'm trying to optimize a query combination. In CF I'd do a select
> > statement and if there was no rows returned from the select, I'd do an
> > insert. Basically I'm making sure a value does not exist before
> > entering it.
> > I'm thinking that putting this logic into a stored procedure might be
> > a good idea. The only problem is, I don't (currently) know how to do
> > the part where I check if the select returns data. Is there a MS SQL
> > function to say queryreturnsdata() or the like? Is there a better way?
> >
> > Thanks
> >
> > --
> > Michael Dinowitz
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3332
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
