MS SQL Dynamic Stored Procedure Using A Datetime Variable
Feb 5, 2004
Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.
What I have is:
CREATE PROCEDURE busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
The error I keep getting is:
Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.
Any ideas appreciated.
(Edit:)
I've also tried it this way:
CREATE PROCEDURE UK_busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN
/* Do some error processing */
PRINT 'Error Occured' END
ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
Which gives me the same error!
.logic.
View 5 Replies
ADVERTISEMENT
Jan 30, 2007
Ok, so I have this stored procedure that Inserts a record and I need to return the ID inserted to pass to another procedure. I ended up having to use sp_executesql because I need to dynamically add the schema to the table. The issue that I am having is that the variable that I try to pass to the other procedure always ends up null, but it shows the correct output. Just to clarify on this, i am an Applications developer and not a DBA so I may be going about this all wrong. Any help on this would be greatly appreciated.
Here is the SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_AddAnalyte]
@Analyt NVarChar(100),
@LName NVarChar(100),
@AtomicNum NVarChar(100),
@AtomicMass NVarChar(100),
@Weight NVarChar(100),
@HalfLife NVarChar(100),
@Units NVarChar(100),
@HF_Secs NVarChar(100),
@Gas NVarChar(100),
@Nat NVarChar(100)
AS
DECLARE @ID INT,@i INT
DECLARE @schema VarChar(50)
DECLARE @sql NVARCHAR(512)
BEGIN
SET @Schema = (SELECT schema_name())
SET @sql = N'INSERT INTO [' + @schema + '].[Analytes] ([Analyte],[LongName],[AnalyteType]) VALUES (''' + @Analyt + ''',''' + @LName + ''',1)';
EXEC sp_executesql @query = @sql;
SET @sql = N'SELECT MAX(AnalytePK) FROM [' + @schema + '].[Analytes]';
EXEC sp_executesql
@query = @sql,
@params = N'@ID INT OUTPUT',
@i = @ID OUTPUT
EXEC sp_AddParameterValue 6, @i, 'AtomicNum', @AtomicNum, '';
EXEC sp_AddParameterValue 6, @i, 'AtomicMass', @AtomicMass, '';
EXEC sp_AddParameterValue 6, @i, 'Weight', @Weight, '';
EXEC sp_AddParameterValue 6, @i, 'HalfLife', @HalfLife, '';
EXEC sp_AddParameterValue 6, @i, 'Units', @Units, '';
EXEC sp_AddParameterValue 6, @i, 'HF_Secs', @HF_Secs, '';
EXEC sp_AddParameterValue 6, @i, 'Gas', @Gas, '';
EXEC sp_AddParameterValue 6, @i, 'Natural', @Nat, '';
END
View 13 Replies
View Related
Jan 20, 2002
I am trying to return a variable from a dynamic SP -
declare @v_outvarchar(400)
...
execute ("select @v_out=" + @v_column + " from " + @v_table_name + " where " + @v_key_sql)
I get the following error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@v_out'.
Any idea how to fix this?
Carl
View 1 Replies
View Related
Nov 1, 2007
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
View 2 Replies
View Related
Apr 28, 2007
In one step of an SSIS package, i create an outgoing XLS filename based on the current datetime setting, a la:
"myFileName_" + (DT_WSTR,4)YEAR( getdate()) + RIGHT("0" + (DT_WSTR,2)MONTH( getdate()),2)+
RIGHT("0" + (DT_WSTR,2)DAY( getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mi", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("ss", getdate()),2) +".xls"
which provides the format as myFileName_yyyymmddhhmmss.xls.
This value is then assigned to a variable, user::myFilenameDateTime.
This variable is referred to in various steps which need the full pathname or filename.
I found, though, that in subsequent steps, the value for user::myFilenameDateTime is re-calculated whenever the variable is invoked.
So in one Task i created the physical output XLS file and named it "correctly", eg, myFileName_20070428090204.xls; in the next Task, i call a Stored Procedure in SQL Server 2005 to email the file with a corresponding message (that pulls in more data from the database).
The single Parameter to the Stored Procedure (that does the emailing) is the supposed/expected full Pathname of the outgoing file just produced but the parameter no longer represents the original filename -- and has changed slightly (a few seconds have been added) and the filename is now, myFileName_20070428090210.xls; consequently, my Email Distribution program cannot find the file with that specific name, although File = myFilename_20070428090204.xls does certainly exist.
so it appears that these variables are calculated realtime whenever encountered.
1) Am I misunderstanding something or misusing the variable assignment?
2) How can i keep << myFileName_yyyymmddhhmmss.xls >>, "static" throughout the duration of the overal SSIS Process.
3) I would think that even if I assign the derived myFileName value initially to ANOTHER "static" variable, this won't achieve anything because the new variable will be re-calculated again, as well, when it is invoked.
thx/spirits,
seth j hersh
View 3 Replies
View Related
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
Sep 13, 2006
two variables declared in my proc:@DATE_RANGE_START as datetime,@DATE_RANGE_END as datetime,When I execute my SP it takes 34 seconds.When I change the variables to:@DATE_RANGE_START1 as datetime,@DATE_RANGE_END1 as datetime,and add this to my sp:declare @DATE_RANGE_START datetimedeclare @DATE_RANGE_END datetimeset @DATE_RANGE_START = @DATE_RANGE_START1set @DATE_RANGE_END = @DATE_RANGE_END1the SP runs in 9 seconds (which is expected)Passing in '1/1/01' and '1/1/07' respectivly.Everything else is equal and non-important to this problem.Why does it take 34 seconds when I use the variables from the inputparameters?Interesting isn't it.Jeff
View 5 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Jun 12, 2006
Hello,
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
The stored proc is expecting datetime parameters.
Thanks for the help.
Mike
View 3 Replies
View Related
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
Mar 27, 2006
Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
View 3 Replies
View Related
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
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
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
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
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
Apr 15, 2004
Hi;
I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??
Create Procedure AddLog
(
@User char(10),
@DLogon DateTime(8),
@Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@User,DLogon,Status)
ERROR:
Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9
The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
View 1 Replies
View Related
Mar 14, 2008
Hi,
Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateActiveStates1]
@PROVIDERID INT
AS
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @ACTIVE BIT
SET @ACTIVE = 0
SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)
SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)
IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))
BEGIN
PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'
SET @ACTIVE=0
END
ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())
BEGIN
IF (@ENDDATE = NULL)
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'
SET @ACTIVE=1
END
ELSE IF (@ENDDATE >= GETDATE())
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'
SET @ACTIVE=1
END
ELSE
BEGIN
PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'
SET @ACTIVE=0
END
END
UPDATE Providers SET Active=@ACTIVE
WHERE ProviderID=@PROVIDERID
UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID
View 2 Replies
View Related
Jan 3, 2008
Hello,
I have got an SQL server stored procedure, and I would like to get this stored procedure, dbo.SpDate_Time_Minute_Today below executed against a matching or exact datetime from the database tables, PRODUCT_SALES.
Code Block
CREATE PROCEDURE dbo.SpDate_Time_Minute_Today
AS
SELECT PROD_SAL_DESC FROM PRODUCT_SALES
WHERE (CONVERT(CHAR(11), PROD_SAL_BY_DATE, 103) + ' ' + SUBSTRING(CONVERT(CHAR(17), PROD_SAL_BY_DATE, 100), 13, 19) = CONVERT(CHAR(11), CURRENT_TIMESTAMP, 103)
+ ' ' + SUBSTRING(CONVERT(CHAR(17), CURRENT_TIMESTAMP, 100), 13, 19))
/* SET NOCOUNT ON */
RETURN
PRODUCT_SALES has columns like this:
PROD_SAL_NO
PROD_SAL_BY_DATE
PROD_SAL_DESC
PROD_SAL_MFR
PROD_SAL_DEPT
Please do anyone here know how I can achieve this please. Thanks.
View 6 Replies
View Related
May 16, 2008
When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".
Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();
SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs
if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}
I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
View 3 Replies
View Related
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
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
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
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
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
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
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
May 25, 2004
Hi all!
I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:
---------------------------------------------------
@crit varchar(100)
SELECT fldID, fldName FROM tblUsers
WHERE @crit
----------------------------------------------------
Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.
cheers!
pelle
View 2 Replies
View Related
Jul 7, 2004
I am trying to do something similar to the following where I want to perform dynamic ordering on two tables that have been unioned as shown below.
CREATE PROCEDURE procedure_name
@regNum varchar(14),
@sortOrder tinyint = 1
AS
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblSPG_Header
WHERE
REG = @regNum
UNION
SELECT Filler_OrdNum As 'Accession', RTrim(Obs_Code) As 'Observation', REG As 'Register',
Obs_Date As 'Observation Date'
FROM tblRCH_Header
WHERE
REG = @regNum
ORDER BY Obs_Date DESC
GO
Note that I am only sorting on the Obs_Date column, but I'd like to be able to sort on any column within the selection list. I know that I need to use:
ORDER BY CASE WHEN @sortOrder = 1 THEN Obs_Date END DESC
but I frequently get the following error when I try to do so:
"ORDER BY items must appear in the select list if the statements contain a UNION operator"
If anyone can offer any suggestions, I would appreciate it. Thanks.
View 1 Replies
View Related
Jul 23, 2004
Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @Path, @Status, @Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.
View 1 Replies
View Related
Aug 17, 2004
Hi
I am getting the following error
Syntax error converting the varchar value 'Select * from Residential WHERE Price BETWEEN ' to a column of data type int.
when running the following SP.
CREATE PROCEDURE testing
(
@Locationnvarchar(100)=NULL,
@TypeHomenvarchar(50)=NULL,
@MinPriceint=0,
@MaxPriceint=9999999999,
@Bedroomsnvarchar(2)=NULL,
@BathsSearchnvarchar(2)=NULL
)
AS
Declare @strSql char(255)
Set @strSql="Select * from Residential WHERE "
Set @strSql=@strSql + "Price BETWEEN " + @MinPrice + " AND " + @MaxPrice
If @Location is NOT NULL
Set @strSql=@strSql + ' AND city = ' + @Location
If @TypeHome is NOT NULL
Set @strSql=@strSql + ' AND Type = ' + @TypeHome
Set @strSql=@strSql + ' AND BDRM >= ' + @Bedrooms
Set @strSql=@strSql + ' AND BATHS <= ' + @BathsSearch
Set @strSql=@strSql + ' AND IDX = Y'
Exec(@strSql)
What is causing this error?
Thanks in advance
View 3 Replies
View Related
Sep 7, 2004
I create a stored procedure as following:
........
CREATE Procedure myAutoSearch
(
@Make varchar(50),
@Model varchar(50),
@AutoType varchar(50),
@Miles float,
@Zipcode varchar(5)
)
AS
DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'
if @RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @Make
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
....................
This stored procedure work very well.
The question is how I add some dynamic condition inside the where condition.
I want to add:
If @Model <> "See All Models"
AND a.Model = @Model'
If @AutoType <> "New/Used"
AND a.Condition = @AutoType
I try several ways, but fail.
If you know how to add these two dynamic parameters into the condition of stored procedure, please help.
View 9 Replies
View Related
Mar 30, 2005
Hi
I am in the very final stages of
building a dating app for a client, I am totally stuck with the
advanced search page. been googling for days with limited success
For the most basic of purposes I have added a few form fields to my search.aspx page;
county (Dropdown list)
min age (Dropdown list)
max age (dropdown list)
Smoker (check box)
keyword (textbox)
My codebehind passes the vars to a stored procedure
@county = me.county.selectedvalue
etc
My problem is the stored procedure I sort of have the following but I can't get it to run
<code>
ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@countyID int ,
@MaxAge
varchar(100),
@MinAge
varchar(100),
@smoker tinyint),
@keyword
varchar(250))
AS
DECLARE @SQL Varchar
(4000)
SELECT @SQL = 'dbo.user_accounts.profileComplete,
dbo.user_accounts.countyID, dbo.user_profiles.smoker,
dbo.user_profiles.Age
FROM dbo.user_accounts INNER
JOIN
dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE (dbo.user_accounts.profileComplete =
1)'
IF @countyID > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_accounts.countyID = @countyID)'
IF @MaxAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MaxAge)
'
IF @MinAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age >= @MinAge)
'
IF @smoker > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_profiles.smoker = 1)'
IF @keyword IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Description LIKE @MinAge)
'
EXEC(@SQL)
</code>
If I can get this to work I can add the remaining fields that I need
Am I Missing something glaringly obvious?
Any help or advice gratefully received
Thanks
View 3 Replies
View Related