sp not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sp not working

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-29 : 06:26:31

Hi i have created an sp to insert values into a table fetching values from different tables
the sp is as follows:

CREATE PROCEDURE USP_VIEW_TASK_UPDATE
(
@TASK_ALLOC_DATE DATETIME,
@TASK_ID VARCHAR(5),
@PROJ_ID VARCHAR(15),
@SUBMOD_ID VARCHAR(15),
@ACTIVITY VARCHAR(15),
@TASK_DETAILS VARCHAR(100),
@TASK_CREATE_BY VARCHAR(15),
@SEND CHAR(1),
@ASSOC_ID VARCHAR(15)
)
AS
BEGIN

INSERT INTO UST_VIEW_TASK_TEMP VALUES
(
@TASK_ALLOC_DATE,
@TASK_ID,
@PROJ_ID,
@SUBMOD_ID,
@ACTIVITY,
@TASK_DETAILS,
@TASK_CREATE_BY,
@SEND
)
SELECT
ALLO.TASK_ALLOC_DATE,
ALLO.TASK_ID,
HIST.PROJ_ID,
HIST.SUBMOD_ID,
HIST.ACTIVITY,
ALLO.TASK_DETAILS,
HIST.TASK_CREATE_BY,
'N'
FROM
UST_TASK_ALLOCATION ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND convert(varchar,ALLO.TASK_ALLOC_DATE,112) =convert(varchar,getdate(),112)
AND HIST.TASK_ID=ALLO.TASK_ID
AND ALLO.TASK_ID=@TASK_ID
END
but the sp is not working it is not inserting values in to it but it is sucessfully executed can any one sort it to me

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:29:21

whats the purpose of select after insert? you're already inserting values from variables from table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-29 : 06:30:04

This SP is not fetching values from different tables for insert.
It's using given VARS for insert and then making a select that has no relation to the insert.
Edit: Arrgh - too late again

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-29 : 06:32:11

then how to modify it

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:37:58

may be this


CREATE PROCEDURE USP_VIEW_TASK_UPDATE
(
@TASK_ALLOC_DATE DATETIME, 
@TASK_ID VARCHAR(5), 
@PROJ_ID VARCHAR(15), 
@SUBMOD_ID VARCHAR(15), 
@ACTIVITY VARCHAR(15), 
@TASK_DETAILS VARCHAR(100),
@TASK_CREATE_BY VARCHAR(15),
@SEND CHAR(1),
@ASSOC_ID VARCHAR(15)
)
AS
BEGIN


INSERT INTO UST_VIEW_TASK_TEMP 
VALUES
(
@TASK_ALLOC_DATE, 
@TASK_ID, 
@PROJ_ID, 
@SUBMOD_ID, 
@ACTIVITY, 
@TASK_DETAILS,
@TASK_CREATE_BY, 
@SEND
)
SELECT 
ALLO.TASK_ALLOC_DATE, 
ALLO.TASK_ID, 
HIST.PROJ_ID, 
HIST.SUBMOD_ID, 
HIST.ACTIVITY, 
ALLO.TASK_DETAILS,
HIST.TASK_CREATE_BY, 
'N' 
FROM 
UST_TASK_ALLOCATION ALLO, 
UST_TASK_HISTORY HIST 
WHERE 
ASSOC_ID =@ASSOC_ID 
AND ALLO.TASK_ALLOC_DATE>@TASK_ALLOC_DATE
AND  ALLO.TASK_ALLOC_DATE<DATEADD(dd,1,@TASK_ALLOC_DATE)
AND HIST.TASK_ID=ALLO.TASK_ID 
AND ALLO.TASK_ID=@TASK_ID
AND HIST.PROJ_ID=@PROJ_ID
AND HIST.SUBMOD_ID=@SUBMOD_ID 
AND HIST.ACTIVITY=@ACTIVITY 
AND ALLO.TASK_DETAILS=@TASK_DETAILS
AND HIST.TASK_CREATE_BY =@TASK_CREATE_BY

END

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-29 : 06:46:06

YA THANK U I TRIED IT BUT ITS COMING INCORRECT SYNTAX NEAR SELECT

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:46:30

and just in case you dont want to pass all parameter always you need this

CREATE PROCEDURE USP_VIEW_TASK_UPDATE
(
@TASK_ALLOC_DATE DATETIME=NULL, 
@TASK_ID VARCHAR(5)=NULL, 
@PROJ_ID VARCHAR(15)=NULL, 
@SUBMOD_ID VARCHAR(15)=NULL, 
@ACTIVITY VARCHAR(15)=NULL, 
@TASK_DETAILS VARCHAR(100)=NULL,
@TASK_CREATE_BY VARCHAR(15)=NULL,
@SEND CHAR(1)=NULL,
@ASSOC_ID VARCHAR(15)=NULL
)
AS
BEGIN


INSERT INTO UST_VIEW_TASK_TEMP 
SELECT 
ALLO.TASK_ALLOC_DATE, 
ALLO.TASK_ID, 
HIST.PROJ_ID, 
HIST.SUBMOD_ID, 
HIST.ACTIVITY, 
ALLO.TASK_DETAILS,
HIST.TASK_CREATE_BY, 
'N' 
FROM 
UST_TASK_ALLOCATION ALLO, 
UST_TASK_HISTORY HIST 
WHERE 
(ASSOC_ID =@ASSOC_ID OR @ASSOC_ID IS NULL)
AND ((ALLO.TASK_ALLOC_DATE>@TASK_ALLOC_DATE
AND  ALLO.TASK_ALLOC_DATE<DATEADD(dd,1,@TASK_ALLOC_DATE))
OR @TASK_ALLOC_DATE IS NULL)
AND HIST.TASK_ID=ALLO.TASK_ID 
AND (ALLO.TASK_ID=@TASK_ID OR @TASK_ID IS NULL)
AND (HIST.PROJ_ID=@PROJ_ID OR @PROJ_ID IS NULL)
AND (HIST.SUBMOD_ID=@SUBMOD_ID  OR @SUBMOD_ID IS NULL)
AND (HIST.ACTIVITY=@ACTIVITY  OR @ACTIVITY IS NULL)
AND (ALLO.TASK_DETAILS=@TASK_DETAILS OR @TASK_DETAILS IS NULL)
AND (HIST.TASK_CREATE_BY =@TASK_CREATE_BY OR @TASK_CREATE_BY IS NULL)

END

this will ensure it will filter only if pass a valid value else it will ignore particular filter condition
Also i dont waht field should be used for filtering with @SEND parameter so include it accordingly

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-29 : 06:49:13

Hi sorry the mistake was with me i forgot to take value from insert now the sp executed successfully but still no value is getting inserted into the table

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:58:49

first comment the INSERT statement alone and run stored procedure and see if it returns any values for parameters passed.

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-29 : 07:04:49

hi thank u so much
its getting inserted now if i want to update the send parameter like t his if i include in the coding
will it be correct

CREATE PROCEDURE USP_VIEW_TASK_UPDATE
(
@TASK_ALLOC_DATE DATETIME,
@TASK_ID VARCHAR(5),
@PROJ_ID VARCHAR(15),
@SUBMOD_ID VARCHAR(15),
@ACTIVITY VARCHAR(15),
@TASK_DETAILS VARCHAR(100),
@TASK_CREATE_BY VARCHAR(15),
@SEND CHAR(1),
@ASSOC_ID VARCHAR(15)
)
AS
BEGIN
INSERT INTO UST_VIEW_TASK_TEMP
SELECT
ALLO.TASK_ALLOC_DATE,
ALLO.TASK_ID,
HIST.PROJ_ID,
HIST.SUBMOD_ID,
HIST.ACTIVITY,
ALLO.TASK_DETAILS,
HIST.TASK_CREATE_BY,
'N'
FROM
UST_TASK_ALLOCATION ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND convert(varchar,ALLO.TASK_ALLOC_DATE,112) =convert(varchar,getdate(),112)
AND HIST.TASK_ID=ALLO.TASK_ID
AND ALLO.TASK_ID=@TASK_ID
AND HIST.PROJ_ID=@PROJ_ID
AND HIST.SUBMOD_ID=@SUBMOD_ID
AND HIST.ACTIVITY=@ACTIVITY
AND ALLO.TASK_DETAILS=@TASK_DETAILS
AND HIST.TASK_CREATE_BY =@TASK_CREATE_BY

UPDATE UST_VIEW_TASK_TEMP SET SEND='Y'
FROM
UST_TASK_ALLOCATION ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND HIST.TASK_ID=ALLO.TASK_ID
AND ALLO.TASK_ID=@TASK_ID
AND HIST.PROJ_ID=@PROJ_ID
AND HIST.SUBMOD_ID=@SUBMOD_ID
AND HIST.ACTIVITY=@ACTIVITY
AND ALLO.TASK_DETAILS=@TASK_DETAILS
AND HIST.TASK_CREATE_BY =@TASK_CREATE_BY
END

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:11:03

it will if modified like below.
but can i ask whats purpose behind both inserting and simultaneously updating values?Also you've decalred parameters like TASK_ALLOC_DATE,SEND etc but havent used them at all. what are they for?


CREATE PROCEDURE USP_VIEW_TASK_UPDATE
(
@TASK_ALLOC_DATE DATETIME, 
@TASK_ID VARCHAR(5), 
@PROJ_ID VARCHAR(15), 
@SUBMOD_ID VARCHAR(15), 
@ACTIVITY VARCHAR(15), 
@TASK_DETAILS VARCHAR(100),
@TASK_CREATE_BY VARCHAR(15),
@SEND CHAR(1),
@ASSOC_ID VARCHAR(15)
)
AS
BEGIN
INSERT INTO UST_VIEW_TASK_TEMP 
SELECT 
ALLO.TASK_ALLOC_DATE, 
ALLO.TASK_ID, 
HIST.PROJ_ID, 
HIST.SUBMOD_ID, 
HIST.ACTIVITY, 
ALLO.TASK_DETAILS,
HIST.TASK_CREATE_BY, 
'N' 
FROM 
UST_TASK_ALLOCATION ALLO, 
UST_TASK_HISTORY HIST 
WHERE 
ASSOC_ID =@ASSOC_ID 
AND convert(varchar,ALLO.TASK_ALLOC_DATE,112) =convert(varchar,getdate(),112) 
AND HIST.TASK_ID=ALLO.TASK_ID 
AND ALLO.TASK_ID=@TASK_ID
AND HIST.PROJ_ID=@PROJ_ID
AND HIST.SUBMOD_ID=@SUBMOD_ID 
AND HIST.ACTIVITY=@ACTIVITY 
AND ALLO.TASK_DETAILS=@TASK_DETAILS
AND HIST.TASK_CREATE_BY =@TASK_CREATE_BY


UPDATE UST_VIEW_TASK_TEMP SET SEND='Y'
FROM UST_VIEW_TASK_TEMP UST,
UST_TASK_ALLOCATION ALLO, 
UST_TASK_HISTORY HIST 
WHERE 
ASSOC_ID =@ASSOC_ID 
AND UST.PK={HIST/ALLOC}.PK
AND HIST.TASK_ID=ALLO.TASK_ID 
AND ALLO.TASK_ID=@TASK_ID
AND HIST.PROJ_ID=@PROJ_ID
AND HIST.SUBMOD_ID=@SUBMOD_ID 
AND HIST.ACTIVITY=@ACTIVITY 
AND ALLO.TASK_DETAILS=@TASK_DETAILS
AND HIST.TASK_CREATE_BY =@TASK_CREATE_BY
END

PK is primary key column. i dont know which tables column UST_VIEW_TASK_TEMP is related to, so you need tomodify accordingly

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-10-29 : 07:17:26

As u told i modified the update part as such

UPDATE UST_VIEW_TASK_TEMP SET SEND='Y'
FROM
UST_VIEW_TASK_TEMP UST,
UST_TASK_ALLOCATION ALLO,
UST_TASK_HISTORY HIST
WHERE
ASSOC_ID =@ASSOC_ID
AND UST.TASK_ID=ALLO.TASK_ID
AND HIST.TASK_ID=ALLO.TASK_ID
AND ALLO.TASK_ID=@TASK_ID
AND HIST.PROJ_ID=@PROJ_ID
AND HIST.SUBMOD_ID=@SUBMOD_ID
AND HIST.ACTIVITY=@ACTIVITY
AND ALLO.TASK_DETAILS=@TASK_DETAILS
AND HIST.TASK_CREATE_BY =@TASK_CREATE_BY but its not working but the sp is excuted successfully value doesnt change

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 07:33:00

replace UPDATE query with SELECT and see if it returns anything.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources