parameter in stored procedure


i have question related to the parameters in a procedure.

I am stuck at a point and the problem i am facing is that.. i have a parameter supplied through a stored procedure. But, Before the stored proc performs the its operation, taking that parameter , i need to check if that value supplied as a parameter, exists in the target table or not. If it exists i should throw and error and if does exist then i need to perform the intended action in the stored proc. how do i do it .

For example :

          ****@corporatecode =  (select corporatecode from cim.asset))****
           print 'error'
      else                                                                                                                          (                                                                                                                                                                                  
   INSERT INTO CIM.ASSET VALUES  (@id,'1','  ',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1)  
    Problem is at the point where i have  the asterisk. I am not sure how do i check the value of the parameter against the target table.
more ▼

asked Aug 24, 2010 at 02:41 PM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I think what you would want would look roughly like

 if exists (select * from cim.asset where corporatecode = @corpratecode)
   Print 'Error'
   insert into cim asset
   values     (@id,'1','  ',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1)  

As a slightly off topic side note, I recommend explicitly listing the columns in your insert statements even where you do not technically have to. This often makes it easier for people unfamiliar with the code to read it and can provide some protection against schema changes.

more ▼

answered Aug 24, 2010 at 02:57 PM

avatar image

15.6k 22 55 38

@TimothyAWiseman I hope you don't mind, I added the begin / end to the if block because it has more than one statement in it.

Aug 24, 2010 at 03:16 PM Oleg

Thanks for catching that! I wrote it off the cuff without testing. It didn't help that I have been working in Python all day.

Aug 24, 2010 at 03:51 PM TimothyAWiseman

Timothy makes a very good recommendation about listing the columns in the insert statements. In SSMS there is a very nice shortcut for spelling out a comma-delimited list of all columns. You can expand the table name on object explorer, grab the folder titled Columns and drag it to the editor window. This will spell out all columns of the table delimited by comma and by the way will not spell out the comma after the last column, so whatever is spelled out is ready to be used.

Aug 24, 2010 at 05:01 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 24, 2010 at 02:41 PM

Seen: 5053 times

Last Updated: Aug 24, 2010 at 02:41 PM

Copyright 2018 Redgate Software. Privacy Policy