May 12, 2003 at 10:10 am
Hi everyone,
I seem to be having a problem with my sp. The task is a user selects the fields they want to see. This a dynamic array type using that array value pass it to a stored procedure Loop through the values and ultimately insert into a temp table
INSERT TBLTempReport (
Loop through
arravalues
)
SELECT
Loop through
arravalues
FROM SourceTable
I started the sp using a sample script from the forum. This is what I have so far
CREATE PROCEDURE spReportSource
@vcList VARCHAR(8000),
@vcDelimiterVARCHAR(8000)
AS
SET NOCOUNT ON
DECLARE@iPosStartINT,
@iPosEndINT,
@iLenDelimINT,
@iExitINT,
@vcStrvarchar(8000),
@vcSqlvarchar(8000)
SET @iPosStart = 1
SET @iPosEnd = 1
SET @iLenDelim = LEN(@vcDelimiter)
SET @vcSQL = 'INSERT ' + TBLTempReportSource + ' (' +
SET @iExit = 0
WHILE @iExit = 0
BEGIN
SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)
IF @iPosEnd <= 0
BEGIN
SET @iPosEnd = LEN(@vcList) + 1
SET @iExit = 1
END
SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)
SET @iPosStart = @iPosEnd + @iLenDelim
END
RETURN 0
I'm not sure how to proceed in setting up the SELECT statement using the same functionality. I would appreciate any help
Thanks
JMC
JMC
May 15, 2003 at 8:00 am
This was removed by the editor as SPAM
May 15, 2003 at 9:56 am
Is the data in a temp table? Do you mean to select back from the temp table? Or select a series of fields from a table based on what the user wants?
Steve Jones
May 16, 2003 at 1:40 am
If the array that is passed to the sp is comma delimted (e.g. col1, col2, col3) then you could use dynamic sql to create the statement:
declare @sqlString nvarchar(4000)
set @sqlstring = N'select ' + @vcList + N' from SourceTable'
print @sqlstring /* For debugging purposes */
exec sp_executesql @sqlstring
If you need to put the data into a temp table, then use can use dynamic sql to create a temp table. Because of scoping issues, you would need to make it a global table which you have to make unique (as it is viewable across processes) using @@spid:
set @sqlstring = N'create table ##' + convert(varchar,@spid) + N'TBLTempReport as ......'
exec sp_executesql @sqlstring.
NB nvarchar is limited to 4000 bytes which is usuallly large enough although your parms are 8000 bytes.
HTH
Jeremy
May 16, 2003 at 1:45 am
Hi,
once again a topic on dynamic sql. For the pros and cons of this approach take a look at http://www.algonet.se/~sommar/dynamic_sql.html
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 16, 2003 at 2:44 am
Frank,
I've read your article and I am a bit confused.
I accept the performance overhead of dynamic SQL (not caching query plans) and the security aspects (giving specific permissions on tables rather than just exec permissions on procedures).
However, I'm not sure whether in this case you are making us aware of the pitfalls of dynamic SQL or suggesting an alternative way?
I use a lot of dynamic SQL in my app and I'm not sure now whether it is the best solution. My users run a about 12 different sets of data each month which summarise data from a database containing around 150 million rows. These summaries can range in size from a tens of thousands of rows up to 100 million rows. So that I can keep the app running whilst creating the data, I have a separate table for each run - this also makes it easy to delete old data (drop table rather using a delete statement).
Where I use dynamic SQL is to select the different tables:
set @sqlstring = N'select col1, col2, col3
from [' + covnert(varchar,@runid) + '_table
where ....
I have had to give users select permission only on the tables (its a reporting app so no need for inserts or updates) as they need it.
I welcome you views on whether I am using dynamic SQL wisely or foolishly.
Jeremy
May 16, 2003 at 3:00 am
Hi Jeremy,
I just wanted to make aware of some pitfalls of dynamic SQL, because simply answer a question to use dynamic SQL implicates considerations on the aspects you've mentioned (eg. security). The danger that someone intentionally or unintentionally manipulates data is higher with explicit permission on the tables than with permissions on a stored procedure.
For my point of view with SQL2k there isn't really a performance issue with dynamic SQL.
I don't have an alternative way for dynamic SQL. There are several cases I use this for myself, but that are mostly administrative tasks. So I know (hopefully) what I'm doing. But I've seen numerous posting explaining how to use CASE in a stored proc and I think that using CASE is an alternative in many cases to dynamic SQL.
I can't tell whether the use is wise or foolish, it seems to be a classical case for the answer : "It depends...".
So if you're solution works for you, what could be better?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 16, 2003 at 3:14 am
Frank,
Thanks
Jeremy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply