Store procedure

• mcbtiger420

  SSC Journeyman

  Points: 76

  More actions

  May 24, 2010 at 12:23 pm

  #221444

  

  i create store procedure is not work please Solve my problem

  create proc Block

  @date datetime,

  @clientID int,

  @clientName varchar(25),

  @ProductName varchar(25),

  @Quantiy int ,

  @price int,

  @TotalAmount int

  as

  declare @Qurey varchar(50)

  set @Qurey ='Select Status from Clientprofile where ClientID'+@clientID

  if @qurey='Disable'

  begin

  rollback

  end

  else

  if @qurey='Enable'

  begin

  insert into DailyClientTranscation (Dates,ClientID,ClientNames,ProductName,Quantity,Price,TotalAmount)values

  (@date,@clientID ,@clientName ,@ProductName ,@Quantiy ,@price ,@TotalAmount )

  end

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  May 24, 2010 at 12:37 pm

  #1171715

  In your stored proc you just have to use one insert statement

  insert into DailyClientTranscation (Dates,ClientID,ClientNames,ProductName,Quantity,Price,TotalAmount)

  Select @date,clientID ,@clientName ,@ProductName ,@Quantiy ,@price ,@TotalAmount from clientprofile

  where clientID = @clientID and status ='Enabled'

  -Roy

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  May 24, 2010 at 12:49 pm

  #1171719

  This statement:

  set @Qurey ='Select Status from Clientprofile where ClientID'+@clientID

  isn't doing what you think it's doing. It's not executing the query, it's just setting the variable equal to the string. So @qurey isn't equal to either 'disable' or 'enable'.

  Try:

  select @Qurey = Status from Clientprofile where ClientID = @clientID

  ---

  And then again, I might be wrong ...
  David Webb