parameter in stored procedure

Question

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.

Answer 1

0

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.

answered Aug 24 '10 at 02:57 PM

TimothyAWiseman
14.3k ● 16 ● 20 ● 29

@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 '10 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 '10 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 '10 at 05:01 PM Oleg

(comments are locked)

parameter in stored procedure

Follow this question

Related Questions