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
)
AS

SET NOCOUNT ON

DECLARE @Sql_sri   NVARCHAR(4000),
@Sql_mr    NVARCHAR(4000),
@Paramlist NVARCHAR(4000),
@First_Id  INT,
@StartRow  INT


SET ROWCOUNT @StartRowIndex
SELECT @Sql_sri = '
SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuws
WHERE 1 = 1'

IF @SearchQuery IS NOT NULL
SELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)'
    
        IF @CategorieId IS NOT NULL
SELECT @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.Categorie
FROM       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 NULL
SELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)'
    
     IF @CategorieId IS NOT NULL
SELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'

     SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'

IF @Debug = 1
PRINT @Sql_mr 

SELECT @Paramlist = '@xSearchQuery NVARCHAR(100),
     @xCategorieId INT'

EXEC sp_executesql   @Sql_sri, @Paramlist,
     @SearchQuery, @CategorieId

EXEC sp_executesql   @Sql_mr, @Paramlist,
     @SearchQuery, @CategorieId

 

View 8 Replies


ADVERTISEMENT

Dynamic Query With Variables In Store Procedure

May 18, 1999

Hi!
Here is a snap form my code

declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")

I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"

Can someone help me on how to assign a value to a local variable from a dynamic query.

Thank for any help in advance
Hank Lee

View 1 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 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

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

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

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 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

Multiple Stored Procedure...or 1 Dynamic Procedure?

Jul 3, 2007

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
Justin

View 2 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

Dynamic Where In Stored Procedure Help

Sep 7, 2004

Hi all,

I have a web application that has a search engine that returns records based off what the user selects in the search engine. I am currently using coalesce in the where statement in my stored procedure to return the records. For eample,
where field1= coalesce(@parm1,field1). I don't know if this example is better than building the sql statement dynamically in a parameter then executing the parameter with sp_executesql. Can someone explain to me which is better or if there is a better solution?

Thanks,

James

View 5 Replies View Related

Stored Procedure With Dynamic Sql

Aug 6, 2004

CREATE PROCEDURE ggg_test_sp
@start_date datetime,@end_Date datetime
AS

SET NOCOUNT ON
DECLARE @sqlstmt varchar(1000)

SELECT @sqlstmt='SELECT * FROM ggg_emp WHERE date_join BETWEEN ' +CONVERT(varchar(10),@start_date-1,101) + ' AND ' +CONVERT(varchar(10),@end_Date+1,101)

SELECT @sqlstmt
EXEC (@sqlstmt)

GO


I want to apply date filter in the above sp with dynamic sql stmt. When i execute the above procedure with date ranges( @start_date=07/06/2004 AND @end_Date= 08/06/2004)i am not getting any result because my @sqlstmt variable has the select stamet

SELECT * FROM ggg_emp WHERE date_join BETWEEN 07/06/2004 AND 08/06/2004

BUT it should have the sqlstmt as

SELECT * FROM ggg_emp WHERE date_join BETWEEN '07/06/2004' AND '08/06/2004' to produce the required result

I know that for the above SP we dont need any dynamic sql but this is just an example.

So anyone can help me on this issue.

Thanks.

View 1 Replies View Related

Dynamic WHERE In Stored Procedure

Sep 29, 2007

Can anyone help me with this dumb question?
I want to use a stored procedure to bring back a recordset depending if a bit column is set to 1. My table has a number of columns that are of Data Type bit and I want to be able to specify which particular column I'm interested in as a parameter when I call the Stored Procedure.

I have set up the Stored Procedure as follows:


CREATE PROCEDURE getProducts
@param1 varchar(50)
AS
SELECT ProductID, ProductName
FROM dbo.Products
WHERE @param1 = '1'
GO


I'm calling it like this:


Dim cmdX, cmdParam, rsX
cmdParam = "OnSpecial"

set cmdX = Server.CreateObject("ADODB.Command")
cmdX.ActiveConnection = conn_STRING
cmdX.CommandText = "dbo.getProducts"
cmdX.Parameters.Append cmdX.CreateParameter("@RETURN_VALUE", 3, 4)
cmdX.Parameters.Append cmdX.CreateParameter("@param1", 200, 1,50,cmdParam)
cmdX.CommandType = 4
cmdX.CommandTimeout = 0
cmdX.Prepared = true
set rsX = cmdX.Execute
rsX_numRows = 0


I know for a fact that I have products in my dbase with the bit column 'OnSpecial' set to 1, yet no records are coming back.

Any pointers would be most appreciated.

View 3 Replies View Related

Help With Dynamic SQL Stored Procedure

Jul 23, 2005

I have a stored procedure spGetAccessLogDynamic and when I try to callit I get the following error:Server: Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'S'.I dont know why because I dont have anything refering to storedprocedure 'S'I have ran my SQL String with sample values and it works fine. So Iam presuming that it is some kind of syntax error in my storedprocedure but have tried everything and cant find it!Anyway here is the sample data I am using to call it and my spExec spGetAccessLogDynamic '24', '2005/07/04 00:00:00 AM', '2005/11/0400:00:00 AM', 'TimeAccessed DESC'CREATE PROCEDURE spGetAccessLogDynamic(@PinServiceID varchar (4),@StartDate varchar(40),@EndDate varchar(40),@SortExp varchar (100))AS-- Create a variable @SQL StatementDECLARE @SQLStatement varchar-- Enter the Dynamic SQL statement into the variable @SQLStatementSELECT @SQLStatement = ( 'SELECT A.PinValue,A.TimeAccessed,C.Forename, C.SurnameFROM AccessLog A, Members C, Pins PWHERE P.PinValue = A.PinValue ANDP.MemberID = C.MemberID AND A.PinServiceID= ''' + @PinServiceID + '''AND A.TimeAccessed BETWEEN dbo.func_DateMidnightPrevious( ''' +@StartDate + ''' ) AND dbo.func_DateMidnightNext( ''' + @EndDate+''')GROUP BY A.PinValue,A.TimeAccessed, C.Forename, C.SurnameORDER BY ' + @SortExp)-- Execute the SQL statementEXEC ( @SQLStatement)GOAny help would be very very much appreciated!!!!!!ThanksCaro

View 2 Replies View Related

Dynamic SQL Stored Procedure

Dec 4, 2007



We are continuing to have issues with a certain stored procedure using dynamic sql. The issue arose when we tried to clean the stored procedure up, and seemed to have zero problems in staging. As soon as we moved it into production, the stored proc caused excessive blocking and completely slowed down our production environment. We immediately rolled back the older version and production is back to normal.

After looking at the new procedure I don't understand how it could cause blocking. Any help is much appreciated!

Old Proc without issues----
--------
USE [Realist_Prod_1203]
GO
/****** Object: StoredProcedure [dbo].[USP_GetMatchedMLSRecord] Script Date: 12/04/2007 09:33:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
=====================
Created By: Sunil/Sudeep 19-11-2003
Description:
Does a lookup of MLS Property data for reverse link. This is susceptible to error in that if erroneous data is given
to us,it will not find a match. For this reason, commented out the lookup on Suffix and changed the street
to use a like clause. Many users are putting the suffix in the street clause and no hits are generated.
This hurts performance, but it improves the hit ratio.

Usage: exec USP_GetMatchedMLSRecord 61,'3951','','KENSINGWOOD','DR','3951','columbus','OH','43230','39049','600-260368','600-260368-00','6000260368','urlll'

Mods:
01/08/2004 - Balawant - Added nullif(), as it was comparing apn numbers with '' (empty space)
02/23/2004 - Balawant - Added or (or State = '') condition for state, zip, city, StreetDirection and Suffix.
11/18/2004 - Sunil Padmanbhan - Added begin-end and modified altapn and parcelid in nullif statment.
04/03/2007 - Shiny - changed to Parameterized query generation
04/03/2007 - Vasan - Removed redundant nullif's and added a limit of 100 records on output
04/03/2007 - Shiny - Removed more Nullif's and changed datatypes for Zip and CountyID to Char to match with table datatypes
04/05/2007 - Vasan - Modified to match resultsets with original procedure
=====================
if exists (select 1 from sysobjects where name = 'USP_GetMatchedMLSRecord')
drop procedure USP_GetMatchedMLSRecord
grant exec on USP_GetMatchedMLSRecord to webuser
*/
CREATE PROCEDURE [dbo].[USP_GetMatchedMLSRecord]
(
@GroupID int,
@HouseNumber varchar(50),
@StreetDirection varchar(50),
@StreetName varchar(50),
@Suffix varchar(50),
@Unit varchar(50),
@City varchar(50),
@State varchar(50),
@ZIP char(50),
@FIPS varchar(10),
@ApnNumber varchar(50),
@AltApn varchar(50),
@ParcelId varchar(50),
@ReverseLinkURL varchar(200)
)
AS
DECLARE @CountyID char(6)
Select @CountyID=CountyID from ltCounties where FIPS=@FIPS
IF (@ApnNumber IS NOT NULL AND @ApnNumber <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID=@GroupID ))
SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK)
WHERE APNnumber=@ApnNumber AND GroupID = @GroupID ;
ELSE
BEGIN
IF (@AltApn IS NOT NULL AND @AltApn <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@AltApn AND GroupID=@GroupID))
SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK)
WHERE APNnumber= @AltApn AND GroupID=@GroupID;
ELSE
IF (@ParcelId IS NOT NULL AND @ParcelId <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WHERE APNnumber=@ParcelId AND GroupID=@GroupID ))
SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK)
WHERE APNnumber= @ParcelId AND GroupID=@GroupID;
ELSE
BEGIN
-- Finalize parameter values
IF @ReverseLinkURL IS NULL SET @ReverseLinkURL = '';
IF @StreetName IS NOT NULL AND @StreetName <> '' SET @StreetName = @StreetName + '%';
-- Build up SQL text dynamically, only including filter predicates for those parameters that the user wants
-- to search on.
DECLARE @sqltext nvarchar(4000)
SET @sqltext = 'Select top 100 '''' + @ReverseLinkURL as ''ReverseLinkBaseURL'',MLSNumber,Comment
from tblMLSListing WITH (NOLOCK)
where '
-- Because of skew and relative few group IDs, you may want to use an inline literal for this one parameter
-- to avoid plan sharing across different GroupIDs. Use explicit parameterization for the other parameters.
if @GroupID is null set @sqltext = @sqltext + '1=1' --ignore Group_ID if null
else SET @sqltext = @sqltext + 'GroupID=' + CONVERT (varchar(30), @GroupID) + ' ' ;
--House number is mandatory: IF @HouseNumber IS NOT NULL AND @HouseNumber <> ''
SET @sqltext = @sqltext + ' AND HouseNumber=@HouseNumber '
IF @StreetDirection IS NOT NULL AND @StreetDirection <> '' SET @sqltext = @sqltext + ' AND (StreetDirection=@StreetDirection or @StreetDirection='''') '
IF @StreetName IS NOT NULL AND @StreetName <> '' SET @sqltext = @sqltext + ' AND StreetName like @StreetName '
IF @Suffix IS NOT NULL AND @Suffix <> '' SET @sqltext = @sqltext + ' AND (Suffix=@Suffix or Suffix='''') '
--Unit is mandatory: IF @Unit IS NOT NULL AND @Unit <> ''
SET @sqltext = @sqltext + ' AND Unit=@Unit '
IF @City IS NOT NULL AND @City <> '' SET @sqltext = @sqltext + ' AND (City=@City or City='''') '
IF @State IS NOT NULL AND @State <> '' SET @sqltext = @sqltext + ' AND (State=@State or State='''') '
IF @ZIP IS NOT NULL AND @ZIP <> '' SET @sqltext = @sqltext + ' AND (ZIP=@ZIP or ZIP='''') '
--CountyId is mandatory: IF @CountyID IS NOT NULL AND @CountyID <> ''
SET @sqltext = @sqltext + ' AND CountyID=@CountyID '
-- Execute as an explicitly parameterized query. This will provide plan reuse for any executions of the proc
-- that have the same @GroupID and the same combination of non-empty parameters.
/*print @sqltext
print '@ReverseLinkURL = ' + @ReverseLinkURL
print '@HouseNumber = ' + @HouseNumber
print '@StreetDirection = ' + @StreetDirection
print '@StreetName = ' + @StreetName
print '@Suffix = ' + @Suffix
print '@Unit = ' + @Unit
print '@City = ' + @City
print '@State = ' + @State
print '@ZIP = ' + @ZIP
print ' @CountyID = ' + @CountyID
print 'debug: ApnNumber = ' + @ApnNumber*/

EXEC sp_executesql
@sqltext,
N'@ReverseLinkURL varchar(200), @HouseNumber varchar(50), @StreetDirection varchar(50), @StreetName varchar(50),
@Suffix varchar(50), @Unit varchar(50), @City varchar(50), @State varchar(50), @ZIP varchar(50), @CountyID varchar(50)',
@ReverseLinkURL=@ReverseLinkURL, @HouseNumber=@HouseNumber, @StreetDirection=@StreetDirection, @StreetName=@StreetName,
@Suffix=@Suffix, @Unit=@Unit, @City=@City, @State=@State, @ZIP=@ZIP, @CountyID=@CountyID
END
END

New Proc WITH Blocking issues----
--------
/*
=====================
Created By: David Barrs 8-13-2002
Description: Returns the properties for given group id

Usage:
EXEC USP_GetMatchedMLSRecord 1,'8108','','dunn','','','austin','TX','','48453','','','','http://sef.mlxchange.com/reverselink.asp?action=reverselink'
Mods:
xx/xx/xxxx - who - Description
11/28/2007 - Shiny - Refactored the procedure
\\\\\\
=====================
if exists (select 1 from sysobjects where name = 'USP_GetMatchedMLSRecord')
drop procedure USP_GetMatchedMLSRecord
grant exec on USP_GetMatchedMLSRecord to webuser
*/
ALTER PROCEDURE [dbo].[USP_GetMatchedMLSRecord]
(
@GroupID int,
@HouseNumber varchar(50),
@StreetDirection varchar(50),
@StreetName varchar(50),
@Suffix varchar(50),
@Unit varchar(50),
@City varchar(50),
@State varchar(50),
@ZIP char(50),
@FIPS varchar(10),
@ApnNumber varchar(50),
@AltApn varchar(50),
@ParcelId varchar(50),
@ReverseLinkURL varchar(200)
)
AS
DECLARE
@sqltext nvarchar(4000),
@paramlist nvarchar(4000),
@CountyID char(6)
Select @CountyID=CountyID from ltCounties where FIPS=@FIPS
IF (@ApnNumber IS NOT NULL AND @ApnNumber <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@ApnNumber AND GroupID=@GroupID ))
SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK)
WHERE APNnumber=@ApnNumber AND GroupID = @GroupID ;
ELSE
BEGIN
IF (@AltApn IS NOT NULL AND @AltApn <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WITH (NOLOCK) WHERE APNnumber=@AltApn AND GroupID=@GroupID))
SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK)
WHERE APNnumber= @AltApn AND GroupID=@GroupID;
ELSE
IF (@ParcelId IS NOT NULL AND @ParcelId <> '') AND (EXISTS (SELECT 1 FROM tblMLSListing WHERE APNnumber=@ParcelId AND GroupID=@GroupID ))
SELECT @ReverseLinkURL as 'ReverseLinkBaseURL', MLSNumber,Comment FROM tblMLSListing WITH (NOLOCK)
WHERE APNnumber= @ParcelId AND GroupID=@GroupID;
ELSE
BEGIN
-- Finalize parameter values
IF @ReverseLinkURL IS NULL SET @ReverseLinkURL = '';
IF @StreetName IS NOT NULL AND @StreetName <> '' SET @StreetName = @StreetName + '%';
-- Build up SQL text dynamically, only including filter predicates for those parameters that the user wants
-- to search on.
SELECT @sqltext = 'Select top 100 '''' + @ReverseLinkURL as ''ReverseLinkBaseURL'',MLSNumber,Comment
from tblMLSListing WITH (NOLOCK)
where '
IF @GroupID IS NOT NULL
SELECT @sqltext = @sqltext + 'GroupID=' + CONVERT (varchar(30), @GroupID) + ' '

SELECT @sqltext = @sqltext + ' AND HouseNumber=@HouseNumber '

IF @StreetDirection IS NOT NULL
SELECT @sqltext = @sqltext + ' AND StreetDirection = @StreetDirection '

IF @StreetName IS NOT NULL
SELECT @sqltext = @sqltext + ' AND StreetName LIKE @StreetName + ''%'''

IF @Suffix IS NOT NULL
SELECT @sqltext = @sqltext + ' AND Suffix = @Suffix'

SELECT @sqltext = @sqltext + ' AND Unit=@Unit '

IF @City IS NOT NULL
SELECT @sqltext = @sqltext + ' AND City = @City'

IF @State IS NOT NULL
SELECT @sqltext = @sqltext + ' AND State = @State'

IF @ZIP IS NOT NULL
SELECT @sqltext = @sqltext + ' AND ZIP = @ZIP'
SELECT @sqltext = @sqltext + ' AND CountyID='+ CONVERT (varchar(30), @CountyID)+' '
SELECT @paramlist = '
@GroupID int,
@HouseNumber varchar(50),
@StreetDirection varchar(50),
@StreetName varchar(50),
@Suffix varchar(50),
@Unit varchar(50),
@City varchar(50),
@State varchar(50),
@ZIP char(50),
@FIPS varchar(10),
@ApnNumber varchar(50),
@AltApn varchar(50),
@ParcelId varchar(50),
@ReverseLinkURL varchar(200)'

/*
print '@ReverseLinkURL = ' + @ReverseLinkURL
print '@HouseNumber = ' + @HouseNumber
print '@StreetDirection = ' + @StreetDirection
print '@StreetName = ' + @StreetName
print '@Suffix = ' + @Suffix
print '@Unit = ' + @Unit
print '@City = ' + @City
print '@State = ' + @State
print '@ZIP = ' + @ZIP
print '@CountyID = ' + @CountyID
print 'debug: ApnNumber = ' + @ApnNumber
*/
EXEC sp_executesql @sqltext, @paramlist, @GroupID, @HouseNumber, @StreetDirection, @StreetName,
@Suffix, @Unit, @City, @State, @ZIP, @FIPS, @ApnNumber, @AltApn, @ParcelId, @ReverseLinkURL
END
END;


Thank You,

-D

View 1 Replies View Related

Permissions With Dynamic SQL Within Stored Procedure

Aug 1, 2006

Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may be as secure, but simpler.
PS - Please don't let this degenerate into a conversation about OR mappers. I know that happens a lot on these forums.
 

View 3 Replies View Related

Run Dynamic Query Using Stored Procedure

Aug 16, 2007

Hi,
I need to create a stored procedure, which needs to accept the column name and table name as input parameter,
and form the select query at the run time with the given column name and table name..
my procedure is,
CREATE PROC spTest
@myColumn varchar(100) ,
@myTable varchar(100)
 AS
SELECT @myColumn FROM @myTable
GO
This one showing me the error,
stating that myTable is not declared..
.............as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters..
Plese help me?? Is it possible in stored procedure..
 
 
 
 

View 3 Replies View Related

Dynamic Query In Stored Procedure

Apr 22, 2008

Hi i am trying to make the "userName" section of the code below dynamic as well, how can i do this, the reason being userName will not always be passed through to it. 
 
ALTER PROCEDURE [dbo].[stream_UserFind]

@userName varchar(100),
@subCategoryID INT,
@regionID INT
)ASdeclare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like ' + char(39) + '%' + @UserName + '%' + char(39)
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID  = ' + cast( @subCategoryID as varchar(10))if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId  = ' + cast( @regionID as varchar(10))
print @StaticStr
exec(@StaticStr)
)

View 10 Replies View Related

Dynamic Sql For Count In Stored Procedure

Apr 29, 2008

Hi all,
I'm using sql 2005. Can some one please tell me how to write dynamic sql for count. What i want is that i want to count the number of employees existing for the given department names and get the counted values as output into my vb.net 2.0 project.  If any one know who to write this please send the code.. Please help me.. I want the below code to change to dynamic sql:
 Alter proc GetCountforemp
@DestName varchar(200)=null,
@total int output
as
begin
SELECT @total = Count(distinct Employee.EmployeeID) FROM Employee
INNER JOIN Dest R on R.DestID=Employee.DestID
WHERE R.DestName in ('''+@DestName+''')
end 

View 1 Replies View Related

Trying To Build Dynamic Stored Procedure

Jun 6, 2008

My existing ASP 1.0 site keeps getting hacked using SQL injections.  I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search.
I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck.  Getting either errors or double LIKE statements. Need some help.
string[] SqlKWSrch; 
SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ',');     int AStop = SqlKWSrch.Length;     int i = 0;        foreach( string a in SqlKWSearch )       {           if (i <= AStop)           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND ";           }           else           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' ";           }            i++;       }
1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure
GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion.
PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1
RETURN
 
Thank you
 

View 5 Replies View Related

Dynamic Query In Stored Procedure

Jun 13, 2008

Hi, I have a table with values such as test1, test2, test3, test4, test5.
I need to write a stored procedure with paramater (number TINYINT, number2 TINYINT), the number represents the field that I'm going to select and compare. For example if I pass in (1,5) I will need the fields test1 and test5 and store them in Temp and Temp2. How do I write the following to so it will dynamically select which field to use when passing the parameters?
DECLARE @Temp TINYINT,
DECLARE @Temp2 TINYINT, 
SELECT top 1 Temp = test1, Temp2 = test5 from table

View 4 Replies View Related







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