October 12, 2010 at 3:11 pm
I have a large chunk of code that can be run on different columns. Is there any way to do something like this:
select @col1,@col2
from blahblah
Using a stored procedure with an input parameter of @col1 and @col2? Both would be varchar fields.
October 13, 2010 at 4:21 am
Hi,
Yes, definetly. You have to use dynamic query. First frame a query using the variables supplied to the procedure. Store query to a variable and then execute that using exec (@queryvariable). That will works for you.
Create PROCEDURE Dynamic_Query_Example (@p_category varchar(10))
as
begin
declare @sql_query varchar(max), @p_category_select varchar(max)
SELECT @SQL_QUERY = ' SELECT '+@P_CATEGORY+' FROM EMP'
print @sql_query
exec (@sql_query)
end
Thanks,
Siva Kumar J.
October 13, 2010 at 7:56 am
sivaj2k (10/13/2010)
Yes, definetly. You have to use dynamic query. First frame a query using the variables supplied to the procedure. Store query to a variable and then execute that using exec (@queryvariable). That will works for you.
What happens if I pass the value '1; DROP TABLE EMP; --' as the parameter?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 13, 2010 at 7:57 am
craig-404139 (10/12/2010)
I have a large chunk of code that can be run on different columns. Is there any way to do something like this:select @col1,@col2
from blahblah
Using a stored procedure with an input parameter of @col1 and @col2? Both would be varchar fields.
Can you give a small example to show why you need to do this?
Properly constructed (i.e. safe) dynamic SQL is probably the solution, but it would be nice to have an example to illustrate the technique with.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 13, 2010 at 7:59 am
Paul White NZ (10/13/2010)
sivaj2k (10/13/2010)
Yes, definetly. You have to use dynamic query. First frame a query using the variables supplied to the procedure. Store query to a variable and then execute that using exec (@queryvariable). That will works for you.What happens if I pass the value '1; DROP TABLE EMP; --' as the parameter?
To a VARCHAR(10)? An error 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 13, 2010 at 8:26 am
Paul White NZ (10/13/2010)
craig-404139 (10/12/2010)
I have a large chunk of code that can be run on different columns. Is there any way to do something like this:select @col1,@col2
from blahblah
Using a stored procedure with an input parameter of @col1 and @col2? Both would be varchar fields.
Can you give a small example to show why you need to do this?
Properly constructed (i.e. safe) dynamic SQL is probably the solution, but it would be nice to have an example to illustrate the technique with.
I have some code that finds sequences of numbers contained in 2 columns in a database. But these 2 columns can be named differently depending on the database it is being run against. This would only be accessible and ran from the application by sys admins. And by that I mean *maybe* 3 people.
In addition to the columns being selected by the stored procedure, I also need to use the same input parameters in my join statement (which I'm not sure if it can be done)? and my where clause.
October 13, 2010 at 8:30 am
Chris Morris-439714 (10/13/2010)
To a VARCHAR(10)? An error 😎
Don't make me come over there, Chris 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 13, 2010 at 8:32 am
Paul White NZ (10/13/2010)
Chris Morris-439714 (10/13/2010)
To a VARCHAR(10)? An error 😎Don't make me come over there, Chris 😛
Tell me - not sure I can afford all the beers I must owe you by now :w00t:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 13, 2010 at 8:40 am
craig-404139 (10/13/2010)
I have some code that finds sequences of numbers contained in 2 columns in a database. But these 2 columns can be named differently depending on the database it is being run against. This would only be accessible and ran from the application by sys admins. And by that I mean *maybe* 3 people.In addition to the columns being selected by the stored procedure, I also need to use the same input parameters in my join statement (which I'm not sure if it can be done)? and my where clause.
I was kinda hoping for some example data and code, but never mind - I get the idea.
Can you not write the procedure to act on a view, and simply redefine the view to point to the right data?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 13, 2010 at 8:41 am
Chris Morris-439714 (10/13/2010)
Tell me - not sure I can afford all the beers I must owe you by now :w00t:
With the exchange rate the way it is, I could buy you a lifetime of beer for about NZ$20...:-D
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 13, 2010 at 8:44 am
Paul White NZ (10/13/2010)
craig-404139 (10/13/2010)
I have some code that finds sequences of numbers contained in 2 columns in a database. But these 2 columns can be named differently depending on the database it is being run against. This would only be accessible and ran from the application by sys admins. And by that I mean *maybe* 3 people.In addition to the columns being selected by the stored procedure, I also need to use the same input parameters in my join statement (which I'm not sure if it can be done)? and my where clause.
I was kinda hoping for some example data and code, but never mind - I get the idea.
Can you not write the procedure to act on a view, and simply redefine the view to point to the right data?
Sorry didn't know you wanted to see the code. The problem I have is depending on the database the column 'controlnumber' can be called 'begno' or a different name. So my input parameter would have to work in all areas you see controlnumber and same deal with the column endno.
See http://www.sqlservercentral.com/Forums/FindPost1000643.aspx for some more info on what I am trying to do.
Here it is:
--Drop temp table if it exists
IF OBJECT_ID('TempDB..#midpoint','U') IS NOT NULL
DROP TABLE #midpoint
--Create temp table to store text prefix and numerial values of both the control number and endno
CREATE TABLE #MidPoint
(Pattern VARCHAR(100)
,controlnumber varchar(100)
,endno varchar(100)
primary key (pattern,controlnumber))
create index idx_control
on #midpoint (endno)
--Inserting numerical values without text prefix into temp table
INSERT INTO #Midpoint
select left(controlnumber, PatIndex('%[0-9]%', controlnumber)-1) AS Pattern --Strip out text prefix
,SubString(controlnumber, PatIndex('%[0-9]%', controlnumber), 20) AS controlnumber
,ISNULL(SubString(endno, PatIndex('%[0-9]%', endno), 20) --First part of isnull check
,SubString(controlnumber, PatIndex('%[0-9]%', controlnumber), 20)) AS endno --Second part of isnull check
from documents3 as d
--Identify ranges of sequential numbers
select d.pattern + d.controlnumber as 'Start of Sequence'
,d.pattern + (select min(a.endno) as id
from #midpoint as a
left outer join #midpoint as b
on a.endno = b.controlnumber - 1
and a.Pattern = b.Pattern
where b.controlnumber is null
and a.endno >= d.endno
and a.Pattern = (select distinct Pattern
from #MidPoint as m2
where d.Pattern= m2.pattern)) as 'End of Sequence'
from #midpoint as d
left outer join #midpoint as d1
on d.controlnumber - 1 = d1.endno
and d.pattern = d1.Pattern
where d1.endno is null
order by d.pattern
,d.controlnumber asc
*edit*
I should add my code work fine, I just need a way to alter it to add the parameters I need.
October 13, 2010 at 9:03 am
craig-404139 (10/13/2010)
Sorry didn't know you wanted to see the code. The problem I have is depending on the database the column 'controlnumber' can be called 'begno' or a different name. So my input parameter would have to work in all areas you see controlnumber and same deal with the column endno.
There's quite a lot to read on that other thread you referenced. I just skimmed it quickly.
My point is this: instead of using parameters, why not just create a view that points to your source data? You can write your code against the view definition.
When you need to change input tables/databases/column names, simply redefine the view, keeping the column name aliases the same. That way, your procedure works to the fixed 'interface' provided by the view.
Does that make sense to you?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 13, 2010 at 9:11 am
Paul White NZ (10/13/2010)
craig-404139 (10/13/2010)
Sorry didn't know you wanted to see the code. The problem I have is depending on the database the column 'controlnumber' can be called 'begno' or a different name. So my input parameter would have to work in all areas you see controlnumber and same deal with the column endno.There's quite a lot to read on that other thread you referenced. I just skimmed it quickly.
My point is this: instead of using parameters, why not just create a view that points to your source data? You can write your code against the view definition.
When you need to change input tables/databases/column names, simply redefine the view, keeping the column name aliases the same. That way, your procedure works to the fixed 'interface' provided by the view.
Does that make sense to you?
Depending on the database the column name could be different, hence why I need to be able to pass a parameter into the code depending on the database (which the user running the code would know the column name(s)). How can I create a view if the database may or may not have the proper column(s)? I'm obviously missing something here.
October 13, 2010 at 9:22 am
craig-404139 (10/13/2010)
Depending on the database the column name could be different, hence why I need to be able to pass a parameter into the code depending on the database (which the user running the code would know the column name(s)). How can I create a view if the database may or may not have the proper column(s)? I'm obviously missing something here.
Perhaps a code example will make the idea clearer?
-- Original table
CREATE TABLE
dbo.MidPoint
(
Pattern VARCHAR(100) NOT NULL,
ControlNumber VARCHAR(100) NOT NULL,
EndNo VARCHAR(100) NOT NULL,
);
GO
-- View on the table
CREATE VIEW
dbo.SourceData
AS
SELECT Pattern = MP.Pattern,
ControlNumber = MP.ControlNumber,
EndNo = MP.EndNo
FROM dbo.MidPoint MP;
GO
-- Procedure that does the work
-- Only references the view, not the original table
CREATE PROCEDURE dbo.DoStuff
AS
BEGIN
SELECT 'Start of Sequence' = D.Pattern + D.ControlNumber,
'End of Sequence' =
D.Pattern +
(
SELECT id = MIN(A.EndNo)
FROM dbo.SourceData A
LEFT
OUTER
JOIN dbo.SourceData B
ON A.EndNo = B.ControlNumber - 1
AND A.Pattern = B.Pattern
WHERE B.controlnumber IS NULL
AND A.EndNo >= D.EndNo
AND A.Pattern =
(
SELECT DISTINCT
M2.Pattern
FROM dbo.SourceData as M2
WHERE M2.Pattern = D.Pattern
)
)
FROM dbo.SourceData D
LEFT
OUTER
JOIN dbo.SourceData D1
ON D.ControlNumber - 1 = D1.EndNo
AND D.Pattern = D1.Pattern
WHERE D1.endno IS NULL
ORDER BY
D.Pattern ASC,
D.ControlNumber ASC;
END;
GO
-- Run the procedure (no parameters)
EXECUTE dbo.DoStuff;
GO
-- Create a second source data table
-- Notice the slightly different column names
CREATE TABLE
dbo.MidPoint2
(
Pattern VARCHAR(100) NOT NULL,
ControlNo VARCHAR(100) NOT NULL,
EndNumber VARCHAR(100) NOT NULL,
);
GO
-- Alter the view to match the new table
ALTER VIEW
dbo.SourceData
AS
SELECT Pattern = MP.Pattern,
ControlNumber = MP.ControlNo,
EndNo = MP.EndNumber
FROM dbo.MidPoint2 MP;
GO
-- Run the procedure again
EXECUTE dbo.DoStuff;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 13, 2010 at 9:37 am
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply