Post reply

save varible in procedure

  • August 14, 2012 at 10:35 pm

    #392951

    hi,

    i have problem with my storeprocedure :

    ALTER PROCEDURE [dbo].[sp_createdate2]

    @username AS VARCHAR(20)

    AS

    BEGIN

    DECLARE @FirstTable TABLE (id INT IDENTITY NOT NULL PRIMARY KEY,userid INT,tgl DATE,masuk time,pulang time,shift INT,timein TIME,timeout TIME,telat int,psw int,status varchar(20))

    DECLARE @aa as VARCHAR(6)

    set @aa = 'select absen_id from DB_TU.dbo.pokok where USER_NAME = '+ @username +''

    exec @aa

    INSERT INTO @FirstTable

    SELECT (select absen_id from DB_TU.dbo.pokok where USER_NAME = @username ),tdate,'','','','','','','','' from temp_date where tdate <=GETDATE() and DATEPART(weekday, tdate) not in (1,7)

    update a

    set shift = b.num_of_run_id

    from @FirstTable a

    inner JOIN

    (

    select top 100000 userid,num_of_run_id,startdate,enddate from USER_OF_RUN where userid = @aa order by order_run desc

    ) as b

    on a.userid = b.userid and a.tgl >= convert(date,b.startdate ,103) and a.tgl <= convert(date,b.enddate ,103)

    select * from @FirstTable

    END

    when execute there is error :

    [Err] 42000 - [SQL Server]Could not find stored procedure 'select'.

    thanks,

  • August 15, 2012 at 2:01 am

    #1525894

    EXEC(@aa)

    There are a number of errors in this stored procedure - would you be interested in knowing what they are?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply