Assistance with Dynamic SQL and Dynamic Var length 1

[tEkHEd]

Hi there..

I am trying to write an SQL report that takes a user defined variable as a filter (WHERE) clause.

the user inputs the criteria that they want to search for in a delimited string.

I need to understand the best way to identify the number of criteria that the user has specified, and then pass these criteria to another query to be placed in the WHERE clause.


I have the following code already, I just need to find out how to loop this until the end of the string so that I can append to another query..

declare @var varchar(255)
declare @IDLen int
declare @Delim int
declare @NumPatch int

SET @Var = 'ms04-007;ms04-008;ms05-001;ms05-004'
set @idlen = 8
set @delim = charindex(';',@var)
set @numpatch = len(@var)/@idlen

print @delim -- delim char position
print @numpatch -- number of variables in the string

print substring(@var, 1, @delim -1) -- the first variable
set @var = substring(@var, @delim +1, len(@var)) -- reset the string var
print @var -- remainder

Thanks in adv.

 

[dbomrrsm]

see Passing a list of values to a Stored Procedure (Part II) faq183-5207 and

[blue]DBomrrsm[/blue]

 

[donutman]

You might find this helpful, Passing a list of values to a Stored Procedure (Part II) faq183-5207, but I think dynamic SQL is a bad idea for performance and security reasons. Instead, I'd try to define the acceptable filters and write a SP for each type.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[tEkHEd]

Thanks Donutman.

I had to tweak the code a little as I cannot use a SPROC as the SQL is to generate an SMS2003 web report, and they don't support SPROCs.

This is the solution that I can up with.. maybe not the best way, but it works..

DECLARE 
	@VAL VARCHAR(8),
	@IDLen INT,
	@Delim INT,
	@DelimLength INT,
	@NumPatch INT,
	@VarLen INT,
	@Pos INT

SET @VAR = @VAR + ';'
SET @varlen = LEN(@var)
SET @idlen = 8
SET @delim = CHARINDEX(';',@var)
SET @numpatch = LEN(@var)/@idlen
SET @POS = 1

CREATE TABLE #MYVARS ([MS ID] VARCHAR(8))

WHILE NOT @DELIM = 0 BEGIN

	SET @VAL = UPPER(SUBSTRING(@VAR, @POS, @DELIM -1))
	
	INSERT #myvars VALUES (@VAL)
	SET @var = SUBSTRING(@var, @delim +1, LEN(@var) - (@IDLEN + 1))
	SET @varlen = LEN(@var)
	SET @delim = CHARINDEX(';',@var)
	SET @numpatch = LEN(@var)/@idlen
END

SELECT DISTINCT ResourceID, [Name]
INTO #UATCLIENTS
FROM v_CM_RES_COLL_LON003CC --UAT Collection 1
UNION 
SELECT DISTINCT ResourceID, [Name]
FROM v_CM_RES_COLL_LON002EC -- UAT Collection 2
UNION 
SELECT DISTINCT ResourceID, [Name]
FROM v_CM_RES_COLL_LON002ED -- UAT Collection 3

SELECT [NAME] AS HOSTNAME,id0 AS [MS ID],  SUBSTRING(CSYS.UserName0,4, LEN(CSYS.UserName0)) AS USERNAME, 
	CSYS.Manufacturer0 + ' - ' + CSYS.Model0 AS 'SYSTEM TYPE',
	Business_Layer0 AS 'BUSINESS LAYER', Location_Longcode0 AS 'LOCATION',
	(CASE
		WHEN PS.STATUS0 = 'Applicable' AND PS.TIMEAPPLIED0 IS NOT NULL THEN 'REBOOT REQ'
		WHEN PS.STATUS0 ='Applicable' AND PS.TIMEAPPLIED0 IS NULL THEN 'APPLICABLE'
		WHEN PS.STATUS0 = 'Installed' THEN 'INSTALLED'
	END) AS [Status] 
FROM dbo.v_GS_COMPUTER_SYSTEM CSYS 
	INNER JOIN #UATCLIENTS UAT ON CSYS.ResourceID = UAT.ResourceID 
	INNER JOIN v_GS_GEOS_Environment0 GEOS ON UAT.ResourceID = GEOS.ResourceID 
	INNER JOIN V_gs_pATCHSTATE PS ON UAT.ResourceID = PS.resourceid
WHERE id0 in (SELECT * FROM #MYVARS)
ORDER BY [Name]

DROP TABLE #UATCLIENTS

DROP TABLE #MYVARS