Help with a stored procedure - SQL Server Forums

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

Help with a stored procedure

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-11-06 : 10:40:38

I need to build a stored procedure that decides which statement should be ran (either an update or an insert) based off of a value it will receive from an asp.net page.

The value being sent to my stored procedure will be checked against a table to see if that value exists.



I Loop through values in a ListBox and send them to the stored procedure:

storeID and profileID are sent to stored procedure.
 
         if storeID is IN table

             {

              set update parameters: storeID and profileID

              update()

             }

           if storeID is NOT IN table 

             {

              set insert parameters: storeID and profileID

              insert()

             }

Here is how I'm figuring out which storeID's are currently in the Profile Table. I'm coloring those stores red in a page of my project.



select		Store.storeID,
			Store.storeName 

into		#temp2

from		Store
			join Org
				on store.orgID = Org.orgID
			left join [Profile]
				on store.storeID = [Profile].storeID

where		 [Profile].storeID is not null

select Store.storeID,
	   Store.storeName,
	   t.StoreID,
	   (case when t.StoreID is not null then 'Yes' else 'No' end) as inProfile
                                               

from		Store
			join Org
				on store.orgID = Org.orgID
			left join [Profile]
				on store.storeID = [profile].storeID
			left join #temp2 t 
				on store.storeID = t.storeID 

where		Org.orgID = @orgID

drop table #temp2

Not sure if it would help, but here is how I would do it in classic asp w/vb scripting:



Set Conn = Server.CreateObject("ADODB.Connection")
set rs=Server.CreateObject("ADODB.recordset")
Conn.open ConnectionString

sql = "select storeID from [Profile] where storeID = '"&storeID&"'" 
rs.open sql,Conn,3,3

    If rs.recordcount <> 0 Then 

       Update Statement goes here

   else 

       Insert Statement goes here

   end if

Thanks for any help or suggestions. I'm pretty lost as to how to use conditional statements in SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:47:24

[code]IF EXISTS(SELECT 1 FROM Store
join Org
on store.orgID = Org.orgID
left join [Profile]
on store.storeID = [Profile].storeID
WHERE store.storeID=@storeID)
UPDATE Profile
SET ProfileID=@ProfileID
FROM Store
join Org
on store.orgID = Org.orgID
left join [Profile]
on store.storeID = [Profile].storeID
ELSE
INSERT INTO Profile (ProfileID,storeID,..)
VALUES (@ProfileID,@storeID,...)
GO[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 10:53:22

If you are trying to do an UPSERT
use @@ROWCOUNT > 0 IF YOU are using SQL 2008 use MERGE instead

Here's the sudo UPSERT for SQL 2005 and below:


UPDATE statement --Always executes it will not update if not exists which will return a ROWCOUNT of 0
IF @@ROWCOUNT = 0
BEGIN
INSERT STATEMENT  --INSERT only if Update fails
END

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-11-06 : 11:02:13

Thanks guys. I cannot stress how awesome these forums are.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources