Hello all,
Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name???
Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once...
Cheers,
Justin
I need help to create a stored procedure to get the table names from a database and create a Union view. There are over 100 tables.
View 3 Replies View RelatedHi all,Seems like a fundamental question to me but I dont have a definiteanswer for it, Gurus please enlighten me.I have a table 'Table1' whose structure changes dynamically based onsome configuration values from another table. This table is being usedby a program, It was initially used by this program which ran as asingle task (executing at only a specific interval) but now the programhas to be run mutiple times some coinciding with each othe - whichmeant that table structure will change as 2 programs are runningsimultaneously... and therefore I have decided to use seperate tablenames that each has a structure of its now.I use this table name 'Table1' in about 10-15 stored procedures andUDF'sto make the long story short: Since I will not know which table I willbe using in the program I want to pass the table name as an argument tothe SP and UDF's and then access this param in the'select's/updates/inserts' - but this doesn't work unless I use DynamicSQL.Is there any other way of passing table names as parameters and thenusing then in the procs?any ideas will be really helpful.adi
View 5 Replies View RelatedI 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
I need to execute a stored procedure which selects all columns from the passed table. The table used is a variable.
Select * from @Passedtablename. This won't work. Any insights.
Is this possible?
I know this doesn't work but I'll post it anyways so you can see what I'm trying to accomplish.
CREATE PROCEDURE GetStuff
@something int,
@tablename varchar(50)
AS
SELECT * FROM @tablename WHERE something = @something
Like I say, I know it doesn't work...but does anyone know how to accomplish something like this?
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
View 16 Replies View RelatedHi all,
I have been attempting to construct some dynamic SQL using a table name as a parameter, and call it using sp_executesql, in SQL Server 2005.
According to all of the searches and articles I have read today, the following should work:
DECLARE @rownumINT
DECLARE @tabNamNVARCHAR(100)
DECLARE @SQLStringNVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
SET @rownum = 35
SET @tabNam = 'AdminScriptHistory'
SELECT @SQLString = N'SELECT * FROM @table where executionorder=@row'
SET @ParmDefinition = N'@table VARCHAR(100), @row INT'
EXEC sp_executesql @SQLString, @ParmDefinition, @tabNam,@rownum
However, it fails with:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@table".
Anybody know why?
Cheers
I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.
declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)
[Code] .....
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings.
- SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
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
I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure
... but I can not seem to figure out how to dynamically gather the schema of the result set.
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
hi ,
i got this error ... i have tried various scenarios.. nothing works for me... can any one gimme the proper guidance...
The variable name '@CCSID' has already been declared. Variable names must be unique within a query batch or stored procedure.
thanx in advance
raj
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
hai <br>
<p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p>
CREATE PROCEDURE outputtable @tablename nchar(10) AS<br>
begin<br>
.........................<br>
insert into @tablename (classno) values (@cno)<br>
.................................<br>
END<br>
<p> I get the following error message :</p>
Msg 1087, Level 15, State 2, Procedure................<br>
Must declare the table variable "@tablename".<br>
<p>
Is there anyway i can get around this one. I will be grateful if anyone can help me</p>
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
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.
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.
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
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
i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum
(@forum_ID VARCHAR(10), @subject_num INT)
As
If Exists
(SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num)
Return 1
Else
Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
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.
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..
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
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)
)
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
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
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
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
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.
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