x

parameter in stored procedure

All,

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 :

      if(

         ****@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

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(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)
begin
  Print 'Error'
  Return
end
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)  
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

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

@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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1850
x415
x75

asked: Aug 24, 2010 at 02:41 PM

Seen: 3895 times

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