Stored Proc with Dyanmic SQL - Headache
-
Hi
The following code is generateing msg 203 error. The print statement near the foot of the proc outputs an sql statement that runs fine, just not in the context of the proc itself.
Here's the code - All ideas welcome. It doesn't win any prizes for aesthetic beuty but it works in hard-coded form, just wanna make it take a runtime parameter!
ALTER PROCEDURE sp_Extract_Criteria_4 (@Crit_Label AS nvarchar(10))
AS
declare @sql as nvarchar(4000)
set @sql = 'SELECT t1.SetId, t1.StudentId, t1.ExamId,
t1.AssessId, t1.DeptId, t1.ModuleId,
(SELECT CAST(mapValue AS varchar(4))
FROM ASSESSCRITERIA
WHERE critlabel = '''
set @sql = @sql + @crit_label + '''
RIGHT(
LEFT(t1.CriteriaData + t2.criteriadata,
CHARINDEX(
CHARINDEX(
CHARINDEX(
(SELECT CAST(mapValue AS varchar(4))
FROM ASSESSCRITERIA
WHERE critlabel = '''
set @sql = @sql + @crit_label + '''
t1.CriteriaData + t2.criteriadata)) + 1) - 1),
CHARINDEX(
CHARINDEX(
CHARINDEX(
(SELECT CAST(mapValue AS varchar(4))
FROM ASSESSCRITERIA
WHERE critlabel = '''
set @sql = @sql + @crit_label + '''
t1.CriteriaData + t2.criteriadata)) + 1) - 1))
- CHARINDEX(
CHARINDEX (
(SELECT CAST(mapValue AS varchar(4))
FROM ASSESSCRITERIA
WHERE critlabel = '''
set @sql =@sql + @crit_label + '''
t1.CriteriaData + t2.criteriadata))) AS Value
FROM NSTURESULTS t1 LEFT OUTER JOIN
(SELECT studentid, setid, examid, assessid,
groupid, criteriadata
FROM nsturesults
WHERE setid = ''2006-07''
ON t1.StudentId = t2.studentid
WHERE (t1.SetId = ''2006-07'')
(SELECT CAST(mapValue AS varchar(4))
FROM ASSESSCRITERIA
WHERE critlabel = '''
set @sql = @sql + @crit_label + '''
t1.CriteriaData + t2.criteriadata) > 0)'
--print @sql
execute @sql
-
Does the user who runs this ahve access to all the base tables / views / functions used by this dynamic query?
-
Hi,
Just place braces after execute
execute (@sql)
It will work e for you.
Cheers
cheers
-
Wow, great catch... I wouldn't have caught that one soon (sorry barely never use Dynamic sql
 
.But my first question is still valid... All users will need permissions on the base tables to run this procedure.
-
It works a charm with the braces - why are they so important? I do have access rights to all base tables.
For a follow up is there any way to make it simpler.
Here's the scenario, Commercial software product running on SQL SERVER 2K. Stores data in a field up to max length of 40. If data exceeds this length it creates a new line of data and increments the LineNum. I join this to sets of data back together and then parse the data for my specific criteria. This is stored in a key value pair in the new concatentated field. I find the key by looking up the label/key in another table. I then parse the criteria field and spit out the data.
And a huge thanks, only wasted 2 hours on it.
-
Hi Ninja,
You are right... But I think the guy is wel aware of it and the error he mentioned showing that the problem is not with the security right...
Cheers
cheers
-
Stupid question alert : Why not use varchar(4000) instead of 40????
But I think the guy is wel aware of it and the error he mentioned showing that the problem is not with the security right...
Agreed but security is always worth mentionning
? -
It'd be great if the field was huge, but I have no control and the interface app 'validates' the db everytime you logon, checking all tables, fields etc. If it finds something unexpected (a lengthened field) it truncates it without warning!
Not my idea of good practice either!
-
I feel you pain... However I don't see any way of speeding up this thing.
Maybe a concatenation funcion could do it by I don't know your schema enough to create it for you. Here's a sample :
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROM dbo.SysColumns C
WHERE C.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BY C.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
-
Carl,
I see no need for dynamic SQL here as the only thing you are adding to the string is @Crit_Label.
To speed this procedure up I suggest you:
1. Convert to static SQL.
2. Tidy up the code so that it is readable.
3. Get rid of all the nested SELECTs within nested CHARINDEXs.
I spent 20 minutes and came up with the following.
It probably will not work so you will have to do the hard work yourself!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_Extract_Criteria_4
@Crit_Label AS nvarchar(10)
AS
SET NOCOUNT ON
SELECT D.SetId
,D.StudentId
,D.ExamId
,D.AssessId
,D.DeptId
,D.ModuleId
,D.Criteria
,RIGHT(D.LeftBit, LEN(D.LeftBit)
- CHARINDEX (CHAR(10),D.CriteriaDataq + D.Criteriadata2
,CHARINDEX( Criteria, D.CriteriaDataq + D.Criteriadata2)
 
 
AS ValueFROM (
SELECT t1.SetId
,t1.StudentId
,t1.ExamId
,t1.AssessId
,t1.DeptId
,t1.ModuleId
,CAST(A.mapValue AS varchar(4)) as Criteria
,t1.CriteriaData AS CriteriaData1
,ISNULL(t2.CriteriaData, '') AS CriteriaData2
,LEFT (t1.CriteriaData + ISNULL(t2.criteriadata, '')
,CHARINDEX(CHAR(10), t1.CriteriaData + ISNULL(t2.criteriadata, ''),
CHARINDEX(CHAR(10), t1.CriteriaData + ISNULL(t2.criteriadata, ''),
CHARINDEX(CAST(A.mapValue AS varchar(4)), t1.CriteriaData + ISNULL(t2.criteriadata, ''))
 
+ 1) - 1) AS LeftBit
FROM NSTURESULTS t1
JOIN ASSESSCRITERIA A
ON t1.assessid = A.assessid
AND A.critlabel = @Crit_Label
LEFT JOIN nsturesults t2
ON T2.linenum = 1
AND t1.StudentId = t2.studentid
AND t1.SetId = t2.setid
AND t1.ExamId = t2.examid
AND t1.AssessId = t2.assessid
AND t1.GroupId = t2.groupid
WHERE t1.SetId = '2006-07'
AND t1.LineNum = 0
AND t1.ExamId = 'Y11Exam'
AND CHARINDEX(CAST(A.mapValue AS varchar(4)), t1.CriteriaData + ISNULL(t2.criteriadata, '')) > 0 ) D
GO
ps. The face should be a ).
-
With the fixing of a couple of minor typos, it did in fact work and produced exactly the goods desired. Also moved the hardcoded criteria out, they were only a temporary measure anyway.
I was not happy with the nested selects within the CHARINDEX statements but it never occurred to me to move them into the joins of the derived table.
I'm having to revisit this whole thing anyway. It does its job well for now but does not account for the fact that mapvalue=value in some instances. If I'm searching for criteria 120 and this happens to be the value stored against criteria 721 then the whole thing would fall apart. I have not encountered this thus far since must of the data is alphabetic whereas the criteria is always numeric.
Sincethe data is stored in the multivalued field as key/value pairs, I'm now looking at splitting these into a derived table along the lines of
moduleid, ... , key, value
and then simply querying this construct.
-
I am glad my efforts sort of worked!
You are correct, those horrible multi-valued columns need to be dealt with. The following link may give you some ideas:
http://www.sommarskog.se/arrays-in-sql.html
While I do not know the details of your data, my initial inclination would be to look at creating a table-valued function rather than another derived table. Table-valued functions are used to good effect in the above article.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply