SQL Stored procedure problem

Results 1 to 2 of 2

Thread: SQL Stored procedure problem

  1. #1
    Join Date
    Dec 1969

    Default SQL Stored procedure problem

    &nbsp;<BR>Respected ppl,<BR><BR>I am facing the problem in Sql Server stored procedures.<BR><BR>I have two tables, one is head_code having attributes id,head_id and code_id, all r integers and second table is amount having attributes userid, code_id and amt, all these r also integers.<BR><BR>i want to write a store procedure, such that first of all it will select all code_id from head_code table, then it will select * from amount table, if amount table returns nothing then it will insert in amount table userid, code_id and amt.<BR>Remember code_id will be the values selected from head_code table. userid will be the current user session, i m passing it as input parameter to the store procedure. and amt will be the amt = 0.<BR>so amount table should look like this, if suppose userid = 1:<BR><BR>userid code_id amt<BR> 1 1101 0<BR> 1 1102 0<BR> 1 1103 0<BR> 1 1501 0<BR><BR>where all code_id are selected from head_code table.<BR><BR>i don&#039;t know how to keep track of all code_id from head_code table. bcz i can&#039;t handle it.<BR><BR>so pls write syntax for me of this problem.as i m new in stored procedures.<BR>

  2. #2
    Try this procedure Guest

    Default RE: SQL Stored procedure problem

    Try this procedure,<BR><BR>Create Procedure spTest<BR>(<BR> @userid INT<BR>)<BR>As<BR> --set nocount on<BR> <BR> DECLARE @rec_count INT<BR> DECLARE @codeid INT<BR><BR> <BR> SELECT @rec_count=COUNT(*) FROM amount<BR> <BR> IF @rec_count = 0<BR> BEGIN<BR> DECLARE codeid_cur CURSOR FOR<BR> SELECT code_id<BR> FROM head_code<BR> <BR> OPEN codeid_cur<BR> FETCH NEXT FROM codeid_cur INTO @codeid<BR> WHILE @@FETCH_STATUS = 0<BR> BEGIN<BR> INSERT INTO amount (userid,code_id,amt)<BR> VALUES (@userid,@codeid,0)<BR> FETCH NEXT FROM codeid_cur INTO @codeid<BR> END<BR> CLOSE codeid_cur<BR> DEALLOCATE codeid_cur<BR> END<BR> <BR> --set nocount off<BR>RETURN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts