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:3323
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
