Stored Procedure - Local Variables Show As NULL

Mar 18, 2008

 I have a stored procedure where I gather some data and then insert the data into a table variable.  I then attempt to go through each row of the table variable, asign the values to local variables to be inserted into other tables.  However, the local variables show as NULL.BEGIN
DECLARE @tblcontact table
(
SOKey int,
Cntctkey varchar(60),
Cntctownerkey int,
LASTNAME varchar(32),
FIRSTNAME varchar(32),
WORKPHONE varchar(32),
EMAIL varchar(128),
processed int DEFAULT 0
)

INSERT INTO @tblcontact (SOKey, Cntctkey, Cntctownerkey, LASTNAME, FIRSTNAME, WORKPHONE, EMAIL)
SELECT ...

DECLARE @ID int,
@sokey int,
@cntctkey int,
@cntctownerkey int,
@name varchar(65),
@email varchar(128),
@phone varchar(32)

WHILE EXISTS (SELECT * FROM @tblcontact WHERE processed = 0)
BEGIN
SELECT @ID = MIN(SOKey) FROM @tblcontact WHERE processed = 0

SELECT @cntctkey = (CAST(LTRIM(REPLACE(Cntctkey,'CN',' '))AS int)),@cntctownerkey = Cntctownerkey, @name = FIRSTNAME + ' ' + LASTNAME, @phone = WORKPHONE, @email = EMAIL, @sokey = SOKey
FROM @tblcontact
WHERE @ID = SOKey AND @cntctkey <> '43778'

INSERT INTO tciContact (Cntctkey, Cntctownerkey, CreateType, EMailAddr, EmailFormat, EntityType, ExtUser, Name, Phone, UpdateCounter)
VALUES (@cntctkey, @cntctownerkey, '0', @email, '3', '401', '0', @name, @phone, '0')

UPDATE tsoSalesOrder
SET Cntctkey = @cntctkey, UserFld4 = 'temp'
WHERE SOKey = @sokey

UPDATE @tblcontact
SET processed = 1 WHERE @ID = SOKey
END
END 
 

View 4 Replies


ADVERTISEMENT

Local Variables In Stored Proc

Jul 23, 2005

How do you declare and then SELECT a value for a local variable withinstored procedure, increment the value and then use in an Insertstatement? ThanksAny sites that explain this syntax for SQL Server 2000? Thankshals_leftCREATE PROCEDURE [dbo].[InsertQualUnit]@QualRef tinyint,@UnitRef tinyint,@UnitGroupRef tinyint,// this needs to be a local var not an output param, how ?@UnitPosition tinyint OutputAS// Assign a value to the the variable from a SELECT query, how ?SELECT @UnitPosition= SELECT MAX(UnitPosition) FROM tblUnitGroupWHERE QualRef=@QualRef AND UnitRef=@UnitRef ANDUnitGroupRef=@UnitGroupRef// inc the value@UnitPosition+=1// Use the new value in another SQL statementINSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,UnitPosition )VALUES ( @QualRef, @UnitRef, @UnitGroupRef , @UnitPosition)GO

View 4 Replies View Related

Memory Usage For Local Variables In Stored Procedures

Jul 3, 2003

I would like to know if there is a penalty for Varchar variables in stored procedures if I declare them Varchar(8000) instead of Varchar(1000).
I have a lot of variables and sometimes the content will be more them 1000 characters.
Is memory only allocated for the the actual contents or for the complete declared length?

View 2 Replies View Related

Stored Proc: Query Vals To Local Variables

Aug 18, 2004

I am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.

Thats the dream.

The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.

Here is what I have as of this moment for my sp.


ALTER PROCEDURE usp_CalculatePunchcard
AS
DECLARE @dtPP DateTime
SET @dtPP = (SELECT thursday_in1
FROM punchcards
WHERE (punchcard_id = 1))
/*
Also tried....
SELECT @dtPP=thursday_in1
FROM punchcards
WHERE (punchcard_id = 1)
*/

PRINT @dtPP

RETURN
/*
for some reason i can't use GO ... even though every
document i've read on stored procedures has used GO
and none use RETURN
*/


The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '

Any help would be greatly appreciated as I am about to kick someone/something.

Thanks

View 2 Replies View Related

Variables Passed To Oracle Procedure Using SQL Task End With CHR(0) Null

Apr 29, 2008

I am using a Execute SQL Task which takes package variables and passes them to an Oracle SP as parameters:
{call oraclespname(?,?,?)}
where of course the parameters are set up as input parameters on the correct tab in the SQL Task.

Well the data that is being put into the table in Oracle has a CHR(0) or a null appended to the end of it. Of course this makes no sense. Does anyone have an idea of why this is happening or a way to fix it. The data becomes fairly useless as is without doing a RTRIM(data,CHR(0)) on it first which is a work around but kind of a hack.

View 2 Replies View Related

Show Stored Procedure

Jan 11, 2006

Hello,I only have remote access to the db via textual interface.Now I try to find out what an existing stored procedure is doing.I tried like 'desc sp_sale_pro' and 'show sp_sale_pro' - but that is notworking.Can anybody help?Thanks!!Paul

View 3 Replies View Related

Show All Months In First Column Of Stored Procedure

Aug 26, 2005

Hi,I need what would be similar to a cross tab query in Access.First Column down needs to show all the months, column headings wouldbe the day of the month....1 2 3 4 etc...JanFebMaretchow do i set this up in a stored procedure?any help to get me in the right direction would be greatlyappreciated!!thanks,paul

View 2 Replies View Related

Stored Procedure Variables

Sep 19, 2000

Hi!
I am kind of new to the sequel server and I have to write a stored procedure to create raw of my table. I wrote the following stored procedures, but I am not sure weather I can use variables in select statements as in the following procedure. Could you please explain what is the problem, why I am getting an error, when I am trying to create it? <The error says that there is an incorrect syntax near '@tbleName' in lines 21, 25 and 30>

Thank you very much!
Elly.

/* Inserts raw into the talble tbleName. Called from other procs.
tbleName: The name of the table to insert
other variables: values to insert
checkParentId,..: items to be checked upon-parentid, position, parent primary key
Returns:
0 : Successfully inserted a raw into the table
-1 : Position to insert is taken
-16: The specified parent does not exist
-17: Insert was unsuccessful
*/
create procedure sp_acml_createRaw (@tbleName VARCHAR(50), @nameN VARCHAR(50),
@hrefN VARCHAR(50), @posN VARCHAR(50), @parentIdN VARCHAR(50),
@nameV VARCHAR(150),@hrefV VARCHAR(255),
@posV SMALLINT, @parentIdV SMALLINT,
@checkParentId VARCHAR(50), @checkPosition VARCHAR(50),
@checkParentKey VARCHAR(50))
as
BEGIN
/* Check weather the parent exist */
IF exists (select @parentIdN
from @tbleName
where @tbleName.@checkParentKey = @parentIdV) /* if parent exist */
BEGIN
/* Check weather the isertion position is correct */
IF exists (select @checkParentId from @tbleName
where @tbleName.@checkParentId = @parentIdV AND
@tbleName.@checkPosition = @posV)
return -15 /*Insertion position is incorrect - it is taken already*/
/* Insertion postion is correct */
insert into @tbleName(@tbleName.@nameN, @tbleName.@hrefN, @tbleName.@posN, @tbleName.@parentIdN)
values (@nameV, @hrefV, @posV, @parentIdV)
IF (@@ERROR != 0)
return -17 /* Insert was unsuccessful */
return 0
END
return -16 /* The parent specified was not found */
END
/* End of sp_acml_createRaw */

View 2 Replies View Related

Using Variables In Stored Procedure

Aug 25, 2004

I am writing a stored procedure that basically gets a whole lot of info. Simple enough... that part works. Now I want to add functionality to pass in sortBy and direction variables so the results can be sorted from a web page. The code I have is:


Code:


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER procedure GetRecruiterApplicants
@useridvarchar(50),
@trackerGroup varchar(50),
@sortBy varchar(20),
@dir varchar(5)

as
begin

set nocount on

SELECT C.USERID INTO #VU FROM VUSERS V WITH (NOLOCK), COMPANIES C WITH (NOLOCK)
WHERE CHARINDEX(','+C.USERID+',', ','+VUSERS+',')>0 AND V.USERID=@userid
UNION SELECT @userid

SELECT distinct l.AccessCode, l.ApplicantGivenName, l.ApplicantFamilyName, l.DateCreated, l.DateApplicantAccessed, p.ApplicationTitle, l.disabled
FROM chamslinks l, chamsProjectIDs p, chamsGroups g
WHERE l.TrackerID IN (SELECT userid FROM #VU)
AND g.trackerGroup = @trackerGroup
AND p.groupTblID = g.groupTblID
AND p.ProjectID = l.ProjectID
ORDER BY l.@sortBy @dir

DROP Table #VU

set nocount off
end



The error I am getting is: "Server: Msg 170, Level 15, State 1, Procedure GetRecruiterApplicants, Line 24
Line 24: Incorrect syntax near '@dir'."

Any help?

View 5 Replies View Related

Stored Procedure Variables

Aug 20, 2007

i ahve the following procedure

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




create PROCEDURE testflu @p_acad_period nvarchar (5)
AS

SET NOCOUNT ON


/* Base Table fields */
DECLARE @t_student_id nvarchar (11)
DECLARE @full_time_student bit
DECLARE @student_id nvarchar (11)
DECLARE @acad_period nvarchar (5)
DECLARE @aos_code nvarchar (11)
DECLARE @aos_period nvarchar (5)
DECLARE @full_desc_static nvarchar (200)
DECLARE @full_desc_session nvarchar (200)
DECLARE @dept_code nvarchar (10)
DECLARE @aos_start_dt datetime
DECLARE @exp_length smallint
DECLARE @unit_length nvarchar (10)
DECLARE @student_year tinyint
DECLARE @hrs_per_week int
DECLARE @aos_end_dt datetime
DECLARE @no_of_weeks numeric
DECLARE @hrs_total decimal (18,2)
DECLARE @main_course_study_is_C2K tinyint
DECLARE @hrs_actual decimal (18,2)
DECLARE @hrs_actual_student decimal (18,2)
DECLARE @hrs_notional_course decimal (18,2)
DECLARE @hrs_notional_student decimal (18,2)
DECLARE @student_type nvarchar (10)
DECLARE @moa_code nvarchar (10)
DECLARE @qual_aim nvarchar (3)
DECLARE @numeric_qual_aim int
DECLARE @subject_code nvarchar (4)
DECLARE @subject_area_main nvarchar (10)
DECLARE @subject_area_group nvarchar (10)
DECLARE @course_group char (1)
DECLARE @FE_HE_type char (2)
DECLARE @nvq_lvl_ind nvarchar (1)
DECLARE @geolocn_code nvarchar (10)
DECLARE @surname nvarchar (40)
DECLARE @forename nvarchar (40)
DECLARE @birth_dt datetime
DECLARE @post_code nvarchar (8)
DECLARE @student_attend_mode nvarchar (10)
DECLARE @funding decimal (18,2)
DECLARE @key_skill_count smallint
DECLARE @additionality_yn bit
DECLARE @fee_waiver nvarchar (10)
DECLARE @start_date datetime
DECLARE @student_status nvarchar (10)
DECLARE @end_date datetime
DECLARE @spec_learn_disab nvarchar (2)
DECLARE @sldd_ind tinyint
DECLARE @age smallint
DECLARE @full_time_ind tinyint
DECLARE @age_1960_ind tinyint
DECLARE @tsn_ind tinyint
DECLARE @link_ind tinyint
DECLARE @ESOL nvarchar (3)
DECLARE @esol_ind tinyint
DECLARE @infill_ind tinyint
DECLARE @RP_entitlement tinyint
DECLARE @threshold1 datetime
DECLARE @threshold2 datetime
DECLARE @threshold3 datetime
DECLARE @student_full_cost_ind tinyint
DECLARE @fundable_ind tinyint
DECLARE @LP_entitlement tinyint
DECLARE @LP2_entitlement tinyint
DECLARE @LP3_entitlement tinyint
DECLARE @outcome nchar (10)
DECLARE @OP_entitlement tinyint
DECLARE @tenhour_ind tinyint
DECLARE @basicIT nchar (3)
DECLARE @basic_it_ind tinyint
DECLARE @special_needs_ind tinyint
DECLARE @Evening_course tinyint
DECLARE @RP_basic decimal (18,2)
DECLARE @RP_NTETS_weight decimal (18,2)
DECLARE @RP_enhance_NTETS decimal (18,2)
DECLARE @RP_enhance_Age decimal (18,2)
DECLARE @RP_enhance_TSN decimal (18,2)
DECLARE @RP_enhance_IT decimal (18,2)
DECLARE @RP_enhance_SLDD decimal (18,2)
DECLARE @RP_enhance_ESOL decimal (18,2)
DECLARE @RP_total decimal (18,2)
DECLARE @LP_basic decimal (18,2)
DECLARE @subj_area_weight decimal (18,2)
DECLARE @LP_weighted decimal (18,2)
DECLARE @LP_enhance_SLDD decimal (18,2)
DECLARE @LP_enhance_VOC decimal (18,2)
DECLARE @LP_enhance_AGE decimal (18,2)
DECLARE @LP_enhance_TSN decimal (18,2)
DECLARE @LP_enhance_HE decimal (18,2)
DECLARE @LP_enhance_FEE decimal (18,2)
DECLARE @LP_total decimal (18,2)
DECLARE @OP decimal (18,2)
DECLARE @spurs_total decimal (18,2)
DECLARE @student_SPURS_total decimal(18,2)
DECLARE @out_weight decimal (18,2)
DECLARE @main_course_study tinyint
DECLARE @exp_days int
DECLARE @act_days int
DECLARE @total_students bigint
DECLARE @total_enrolments bigint
DECLARE @acad_year_spurs decimal (18,2)
DECLARE @RP_1960 tinyint
DECLARE @RP_null_DOB tinyint
DECLARE @RP_TSN tinyint
DECLARE @RP_disab2 tinyint
DECLARE @RP_disab3 tinyint
DECLARE @RP_disab4 tinyint
DECLARE @RP_ESOL tinyint
DECLARE @LP_1960 tinyint
DECLARE @LP_null_DOB tinyint
DECLARE @LP_TSN tinyint
DECLARE @LP_disab2 tinyint
DECLARE @LP_disab3 tinyint
DECLARE @LP_disab4 tinyint
DECLARE @LP_infill tinyint
DECLARE @LP_infill_1960 tinyint
DECLARE @LP_infill_null_DOB tinyint
DECLARE @LP_infill_TSN tinyint
DECLARE @LP_infill_disab2 tinyint
DECLARE @LP_infill_disab3 tinyint
DECLARE @LP_infill_disab4 tinyint
DECLARE @LP_term2 tinyint
DECLARE @LP_term2_1960 tinyint
DECLARE @LP_term2_TSN tinyint
DECLARE @LP_term2_disab2 tinyint
DECLARE @LP_term2_disab3 tinyint
DECLARE @LP_term2_disab4 tinyint
DECLARE @LP_term3 tinyint
DECLARE @LP_term3_1960 tinyint
DECLARE @LP_term3_TSN tinyint
DECLARE @LP_term3_disab2 tinyint
DECLARE @LP_term3_disab3 tinyint
DECLARE @ndaq_qual nvarchar (12)



DECLARE @base CURSOR


UPDATE delflu
SET tot_students = 0,
tot_enrolments = 0,
flu_total = 0,
processing = 1,
process_start = GETDATE(),
process_end = NULL
WHERE acad_period = @p_acad_period


DELETE FROM delflubase
WHERE (acad_period = @p_acad_period)

SET @total_students = 0
SET @total_enrolments = 0
SET @acad_year_spurs = 0.00

SET @base = CURSOR LOCAL FAST_FORWARD
FOR

SELECT stmaos.student_id, stmaos.acad_period, stmaos.aos_code, stmaos.aos_period, stcstatd.full_desc, stcsessd.full_desc AS SessionDescription,
stcsessd.dept_code, stcsessd.aos_start_dt, stcsessd.exp_length, stcsessd.unit_length, ISNULL(stmaos.student_year, 0) AS student_year,
stcsessd.hrs_per_week / 100 AS hrs_per_week, stcsessd.aos_end_dt, stcsessd.no_of_weeks, stcsessd.hrs_total / 100 AS hrs_total,
stmfesqa.student_type, stcsessd.moa_code, stcfesdt.qual_aim, stcfesdt.subject_code, delsubj.subj_area, ISNULL(delsubj.subj_course_group, 0)
AS subj_course_group, stcfesdt.nvq_lvl_ind, stcsessd.geolocn_code, stmbiogr.surname, stmbiogr.forename, stmbiogr.birth_dt, stmadres.post_code,
stmaos.attend_mode, stmfesqa.fee_override, stmaos.start_date, RTRIM(stmfesqa.course_status) AS course_status, stmfesqa.course_status_dt as end_date,
stmfesqa.spec_learn_disab, FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age, delESOL.subj_code AS ESOL, delspurs.threshold1, /* Log No.132694 - SQL changed*/
delspurs.threshold2, delspurs.threshold3, stmfesqa.outcome, delbasicIT.qual_aim AS basicIT, ISNULL(delsubjarea.subj_area_weight, 0.00)
AS subj_area_weight, ISNULL(deloutwgt.weighting, 0.10) AS weighting, ISNULL(stmaos.additionality_yn, 0) AS additionality_yn,
nicisreports.dbo.ndaqquals.qual_ref
FROM stsacper INNER JOIN
stmaos INNER JOIN
stcsessd ON stmaos.aos_code = stcsessd.aos_code AND stmaos.acad_period = stcsessd.acad_period AND
stmaos.aos_period = stcsessd.aos_period INNER JOIN
stcfesdt ON stmaos.acad_period = stcfesdt.acad_period AND stmaos.aos_code = stcfesdt.aos_code AND
stmaos.aos_period = stcfesdt.aos_period INNER JOIN
stmfesqa ON stmaos.student_id = stmfesqa.student_id AND stmaos.aos_code = stmfesqa.aos_code AND
stmaos.acad_period = stmfesqa.acad_period AND stmaos.aos_period = stmfesqa.aos_period INNER JOIN
stmbiogr ON stmaos.student_id = stmbiogr.student_id ON stsacper.acad_period = stmaos.acad_period AND
stsacper.start_date <= stcsessd.aos_start_dt AND stsacper.end_date >= stcsessd.aos_end_dt INNER JOIN
delspurs ON stmaos.acad_period = delspurs.acad_period INNER JOIN
stcstatd ON stmaos.aos_code = stcstatd.aos_code LEFT OUTER JOIN
deloutwgt ON stcfesdt.nvq_lvl_ind = deloutwgt.nvq_lvl_ind LEFT OUTER JOIN
delbasicIT ON stcfesdt.qual_aim = delbasicIT.qual_aim AND stcfesdt.subject_code = delbasicIT.subj_code LEFT OUTER JOIN
delESOL ON stcfesdt.subject_code = delESOL.subj_code LEFT OUTER JOIN
stmadres ON stmbiogr.student_id = stmadres.student_id AND stmbiogr.perm_add_id = stmadres.add_id LEFT OUTER JOIN
delsubjarea RIGHT OUTER JOIN
delsubj ON delsubjarea.subj_area = delsubj.subj_area ON stcfesdt.subject_code = delsubj.subj_code left outer join
stcstdet on stmaos.aos_code = stcstdet.aos_code inner join
nicisreports.dbo.ndaqquals on replace(stcstdet.text_field1,'/','') = nicisreports.dbo.ndaqquals.qual_ref
where stmaos.acad_period = @p_acad_period
and (stmaos.return_ind = 'F' OR stmaos.return_ind = 'B')
AND (stmaos.stage_ind = 'E')
AND (stcfesdt.qual_aim IS not null)
AND (stcfesdt.qual_aim IS not null)
and (stmfesqa.fund_source <> '09' OR stmfesqa.fund_source IS NULL)
and stmaos.start_date is not null
and stmaos.start_date <= getdate()

OPEN @base
FETCH NEXT FROM @base
INTO @student_id, @acad_period, @aos_code, @aos_period, @full_desc_static, @full_desc_session,
@dept_code, @aos_start_dt, @exp_length, @unit_length, @student_year,
@hrs_per_week, @aos_end_dt, @no_of_weeks, @hrs_total,@student_type,
@moa_code, @qual_aim, @subject_code,@subject_area_group, @course_group,
@nvq_lvl_ind, @geolocn_code, @surname, @forename, @birth_dt, @post_code,
@student_attend_mode, @fee_waiver, @start_date, @student_status, @end_date,
@spec_learn_disab, @age, @ESOL, @threshold1, @threshold2, @threshold3, @outcome, @basicIT,
@subj_area_weight, @out_weight, @additionality_yn,@ndaq_qual

when i run the follwoing i get

exec testflu '06/07'

Server: Msg 16924, Level 16, State 1, Procedure testflu, Line 210
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

i am unsure cos from what i can see i ahve included all, am i missing something

View 7 Replies View Related

Variables In Dynamic SQL In A Stored Procedure

Aug 23, 2007

I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml
The problem with my stored procedure is, is that it doesn't seem to recognize a variable (@First_Id) in my dynamic Sql. With this particular sproc I get the error message: "Must declare the scalar variable '@First_Id'"It seems to be a problem with 'scope', though I still can't yet figure out. Can anyone give me some hints on how to correctly implement the @First_Id in my stored procedure? Thanks in advance!
Here's the sproc:
ALTER PROCEDURE dbo.spSearchNieuws(@SearchQuery NVARCHAR(100) = NULL,@CategorieId INT = NULL,@StartRowIndex INT,        @MaximumRows INT,@Debug BIT = 0)ASSET NOCOUNT ONDECLARE @Sql_sri   NVARCHAR(4000),@Sql_mr    NVARCHAR(4000),@Paramlist NVARCHAR(4000),@First_Id  INT, @StartRow  INTSET ROWCOUNT @StartRowIndexSELECT @Sql_sri = 'SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuwsWHERE 1 = 1'IF @SearchQuery IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)'              IF @CategorieId IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'SELECT @Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'SET ROWCOUNT @MaximumRows SELECT @Sql_mr = 'SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum,                 dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu, dbo.tblBronnen.Video,                dbo.tblCategorieen.CategorieFROM       dbo.tblNieuws INNER JOIN                dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN                dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND                 dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId         WHERE dbo.tblNieuws.NieuwsId <= @First_Id          AND 1 = 1'               IF @SearchQuery IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)'           IF @CategorieId IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'     SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'IF @Debug = 1PRINT @Sql_mr  SELECT @Paramlist = '@xSearchQuery NVARCHAR(100),     @xCategorieId INT'EXEC sp_executesql   @Sql_sri, @Paramlist,     @SearchQuery, @CategorieIdEXEC sp_executesql   @Sql_mr, @Paramlist,     @SearchQuery, @CategorieId 

View 8 Replies View Related

Stored Procedure With 2 Variables, How To Use Them In The WHERE Statement?

Sep 29, 2005

Hi!

I need a stored procedure with this basic setup:

CREATE PROCEDURE test
@Type int
AS

SELECT *
FROM
Cards
WHERE
CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END

GO


I know that the part after WHERE is wrong. But what I would like to achieve is this:

if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.

How can this be done?

Thanks!
/Rickard

View 2 Replies View Related

Stored Procedure Input Variables

Feb 9, 2006

Hi,

I want to convert a SQL query as shown below into a stored procedure:


select name
from namelist
where town in ('A','B','D')


If I want to make the town as the input variable into the stored procedure, how should I declare the stored procedure? As far as I know, stored procedure could only handle individual values, and not a range of values.

Thanks.

View 5 Replies View Related

Table Variables In Stored Procedure

Jan 11, 2006

I am using a table variable inside a stored procedure that I am trying to execute from an OLE Datasource task in IS.  I know there was a problem doing this in DTS, which would result in an Invalid Pointer error.  I am not getting that error, but I am getting an error that says "[OLE DB Source [55]] Error: A rowset based on the SQL command was not returned by the OLE DB provider."  The stored procedure runs fine on it's own.

Any thoughts?

 

 

View 24 Replies View Related

Passing Variables To Stored Procedure

Mar 21, 2008



I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :


create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)


Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?

View 5 Replies View Related

Executing Stored Procedure With Variables

Nov 21, 2006

I have a foreach loop in my SSIS script. I am able to successfully enumerate through an input query. I have a script task inside of my container. I would like to use this task to formulate a Stored Procedure and save this procedrue in a variable so I can use in a future Execute SQL task.

Here is a copy of the code (Which Does Not Work) I am using in the script task to set the variables.

Public Sub Main()

Dim vars As Variables

Dim DropVariable As String

Dim CreateVariable As String

Dim InsertVariable As String

DropVariable = "Execute dbo.[sp_DropTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

CreateVariable = "Execute dbo.[sp_CustomTables] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

InsertVariable = "Execute dbo.[sp_InsertTable] '" + RTrim(Dts.Variables("varTable").Value.ToString) + "'"

Dts.VariableDispenser.LockOneForWrite("varDropTable", vars)

Dts.VariableDispenser.LockOneForWrite("varCreateTable", vars)

Dts.VariableDispenser.LockOneForWrite("varInsertTable", vars)

Dts.Variables("varDropTable").Value = DropVariable

Dts.Variables("varCreateTable").Value = CreateVariable

Dts.Variables("varInsertTable").Value = InsertVariable



'MsgBox(Dts.Variables("varDropTable").Value)

vars.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

View 4 Replies View Related

How To Debug Local Webservice's Stored Procedure From Asp.net

Aug 17, 2007

hi
    i have developed asp.net application which calls  web service of localsystem. it also contains logic for interaction with database. we are also creating connection string through coding not from web.config.  i want to debug stored procedure which is called by webmethod. then pls suggess me how can i debug this stored procedure from asp.net which is called  in local webservice???
  if any one have solve pls help...
atul

View 1 Replies View Related

Order By Clause With Variables In A Stored Procedure

Mar 24, 2004

Hi,
I need to include two input variables
in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me
to do so and gives an Error 1008.
How can i solve this problem?

Thanks for your help!!

View 6 Replies View Related

Tuning Stored Procedure, Variables And Different Optimisers

Nov 15, 2007

HiI've heard 2 things recently, can I confirm if their true/false?(1) If you have a stored procedure and you want to optimise it you cancall exec proc1,you could also use define/set for each of the variables and copy thecode into query analyser,this then makes it easier to tune. However the optimiser worksdifferently for these variables than it does for variables passed intothe query via exec and will produce a less optimalplan(2) There is a different optimiser used in query analyser than thatused otherwise? A colleaguehad a problem where a stored procedure called from dotnet code wasrunning slowly butone run from query analyser via exec, with exactly the same arguments,was running quicklyta

View 1 Replies View Related

Passing System Variables To Stored Procedure

Sep 24, 2007



How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @MachineName, @TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...

Thanks for the help.

View 8 Replies View Related

How To Concatenate Stored Procedure Varchar Variables

Oct 11, 2007

Hi , I am trying to write a stored procedure (i have given it below).i am basically trying to join 4 strings into one based on some if conditions.But the result gives only the intially assaigned string and rest are not getting concatenated.i have provided teh stored procedure below along with the inputs and result i got.Can anyone Please help me to acheive this set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[TestSearch] @distributorId int, @locationId int, @orderTypeId int, @fromDate Datetime = NULL, @toDate Datetime = NULL, @OrderStatus varchar(500) = NULL, @TaxAuthority varchar(500) = NULL, @TaxStampType varchar(500) = NULLASBEGINDeclare @SQL varchar(8000)Set @SQL ='select * from Orders AS a INNER JOINOrderLines AS b ON a.Id = b.FkOrder INNER JOINTaxStampTypes AS c ON b.FkTaxStampType = c.Id where ''' +CONVERT(VARCHAR(8),@fromDate ,1) + ''' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and ''' +CONVERT(VARCHAR(8),@toDate ,1) + '''>= CONVERT(VARCHAR(8), a.OrderDate,1)and a.fkordertype = '+ convert(varchar(1),@orderTypeId) +'anda.FkDistributor = ('+ convert(varchar(50), @distributorId)+',a.FkDistributor)anda.FkLocation in ('+convert(varchar(10),@locationId)+',a.FkLocation)and'IF(@OrderStatus != null)Beginset @SQL= @SQL + 'a.FkOrderState in ('+ @OrderStatus +') and' EndIF(@TaxAuthority!= null)Beginset @SQL = @SQL +'a.FkTaxAuthority in ('+@TaxAuthority+') and'EndIF(@TaxStampType!= null)Beginset @SQL = @SQL + 'c.id in ('+ @TaxStampType+ ')and'End--Execute (@SQL1)select (@SQL);ENDHere is the Input Given to stored Procedure for executing:DECLARE @return_value intEXEC @return_value = [dbo].[TestSearch] @distributorId = 1002, @locationId = 3, @orderTypeId = 1, @fromDate = N'06/10/07', @toDate = N'10/10/07', @OrderStatus = N'2', @TaxAuthority = N'1000', @TaxStampType = N'1000'SELECT 'Return Value' = @return_valueHere Is The Output i get when I execute the stored procedure: select * from Orders AS a INNER JOIN OrderLines AS b ON a.Id = b.FkOrder INNER JOIN TaxStampTypes AS c ON b.FkTaxStampType = c.Id where '06/10/07' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and '10/10/07'>= CONVERT(VARCHAR(8), a.OrderDate,1) and a.fkordertype = 1 and a.FkDistributor = (1002,a.FkDistributor) and a.FkLocation in (3,a.FkLocation) and--Ajay

View 9 Replies View Related

Help Passing A Stored Procedure Reults To A Local Variable?

Apr 29, 2005

I'm trying to do something like this in SQL Server:
<code>
CREATE PROCEDURE sp_insert_proc
(
@item1 as int,
@item2 as varchar(50),
)
DECLARE @LocalVariable AS varchar(50)
SET @LocalVariable = dbo.sp_storedprocedure
INSERT INTO Table
(
Column1,
Column2,
Column3,
)
VALUES
(
@Item1,
@Item2,
@LocalVariable
)
</code>
Is this possible?
How can I return the results of the stored procedure to a variable so I can use those results

View 1 Replies View Related

SQL Server 2008 :: Trigger To Show Which Stored Procedure Has Updated A Table

Jul 9, 2015

I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table

At present I have:

CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)

[Code] ...

However this doesn't appear to bring back the procedure that triggered the update...

The value the trigger should update on is document_status = 0

DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]

[Code] ....

View 9 Replies View Related

Variables As Table Or Column Names In A Stored Procedure

Apr 16, 2008

I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks

View 2 Replies View Related

Retrieve Multiple Variables From Stored Procedure (SQLHelper)

Jun 22, 2004

Hi all,

I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:

ie:

SELECT @Hits = COUNT(DISTINCT StatID) FROM Stats

...etc...

The SP is currently called as below:

SqlParameter[] sqlParams = new SqlParameter[]
{
new SqlParameter("@FromDate", Request["FromDate"]),
new SqlParameter("@ToDate", Request["ToDate"]),
};


My question is this: How do I retrieve these variables?

I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.

Thanks in advance,

Pete

View 5 Replies View Related

Using Stored Procedure As OLE DB Source With Parameters From Package Variables

Apr 4, 2006

Hi Guys,

(I have searched this forum extensively, but still can't find the solution to this problem)

Here it is:

I have step in my ETL process that gets facts from another database. Here is how I set it up:

1) I have to package variables called User::startDate and User::endDate of data type datetime

2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine)

3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate)

When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping."

It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?"

To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well.

Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message.

This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...

View 23 Replies View Related

Please Help To Assign Multiple Results Into Individual Variables, Stored Procedure

Apr 12, 2008

I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line
(@Tester1       integer,@Tester2        integer,@Tester3    integer,@Tester4    integer,@Tester5    integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen:
@Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.

View 2 Replies View Related

Problem Passing Variables As Parameters To Extended Stored Procedure

Jun 20, 2007

Hello all,



I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:



C++ Extended Stored Procedure:

(Basically all this code is doing is retrieving the parameters and printing them back out)



srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);

param1 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);

param1[uLen] = '';



srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);

param2 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);

param2[uLen] = '';



srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);

param3 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);

param3[uLen] = '';



sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);

srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);





Calling the XP with literal values:



EXEC xp_mytest 'one','two','three'



Output:

Params received by xp: one, two, three





Calling XP via a stored procedure:



create procedure sp_mytest

(

@myvar1 nvarchar(200),

@myvar2 nvarchar(50),

@myvar3 nvarchar(50)

)

as BEGIN

PRINT @myvar1

PRINT @myvar2

PRINT @myvar3

EXEC xp_mytest @myvar1, @myvar2, @myvar3

END



EXEC sp_mytest 'one','two','three'



Output:

one

two

three

Params received by xp: o,t,t





Any insight or assistance is greatly appreciated!!!

View 1 Replies View Related

Help For Stored Procedure And Null...

Jul 20, 2005

Hi,I have write a stored procedure which makes update in a numeric (int) field.Depending on data in other table, in some case the result of query get aNull value instead a zero value...How can I tell to Update query to NOT update field if the value is Null ?I hope my word clear...here the stored procedure:UPDATE dbo.ANAUTENTISET dist1punti = dist1punti +(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepuntiFROM TEMPIMPORTAZIONEDIST1 INNER JOINANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =ANAARTICOLI.codartdist1WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))WHERE (piva IN(SELECT pivaFROM TEMPIMPORTAZIONEDIST1GROUP BY piva))Thanks in advancePieroItaly

View 2 Replies View Related

Integration Services :: Assign Variables To Multiple Table Results From Stored Procedure

Sep 21, 2015

If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?

View 6 Replies View Related

Outputstring Stored Procedure Always Null

Oct 24, 2006

Can anyone tell me why my outstring is always null with this table structure:1 This is my sql table structure
2
3 FK = ID int,
4 Empnaam varchar(200),
5 PK = EmpID int
 and with this stored procedure: ALTER PROC [dbo].[ShowHierarchy] @Root int,@outstring VARCHAR(8000) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpNaam varchar(30)

SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root)
SET @outstring= @outstring+REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam +'
'
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root)

WHILE @EmpID IS NOT NULL BEGIN
EXEC dbo.ShowHierarchy @EmpID,@outstring OUTPUT
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root AND EmpID > @EmpID)
END
END I use this code in visual studio:this is my function :1
2 public static List<string> viewtree(int Root)
3 {
4 SqlConnection con = new SqlConnection(mainConnectionString);
5 con.Open();
6 try
7 {
8 List<string> ids = new List<string>();
9 SqlCommand command = new SqlCommand(@"ShowTree", con);
10 command.Parameters.AddWithValue("@Root", Root);
11 command.Parameters.Add(new SqlParameter("@outstring", SqlDbType.VarChar, 8000));
12 command.Parameters["@outstring"].Direction = ParameterDirection.Output;
13 command.CommandType = CommandType.StoredProcedure;
14 SqlDataReader dr = command.ExecuteReader();
15 command.Parameters.Clear();
16 while (dr.Read())
17 {
18
19 ids.Add(dr["@outstring"].ToString());
20
21 }
22
23
24 return ids;
25 }
26 finally
27 {
28 con.Close();
29 }
30 }
  It would be awesome if someone could test this and help me out....Thanks in advance

View 28 Replies View Related

Stored Procedure With Bit Parameter As Null

Dec 16, 2013

I'm trying to write a stored procedure for a search criteria. A user will have few controls on ASP.Net webpage to enter/select the search criteria, based on which , it returns the data.

A Textbox( to enter Username), A Textbox (to enter Employee name),
A DropDownList (To select the profile. Its Id is sent as parameter) &
A DropDownList (To select status). This is where I am stuck.

The last dropdown has 3 list items
1. --All-- value is null/empty
2. Active value is 0
3. Blocked value is 1

Though the Database table will have only 0's and 1's for the status column, the user should be able to see all records when -all-- is selected.I also tried excluding the status parameter and its condition, but no luck either way.

View 3 Replies View Related

Stored Procedure Gives Null Values

Jan 18, 2007

hi this is my stored procedure.i am passing mu column nam and recordname has to be fetched.if run this proceedure i am getting null records only.but i am having records in my table

CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)

)
As
if(@columnname !=' ' and @recordname !=' ')
begin
select userid,user_name,password,role_code,expiry_date from usermaster where '+@columnname+' like '+@recordname+"%"'
end
GO

can any one help to solve this please

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved