Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Internal Variables in the Workflow Query/Script Generator

Created: 11 Jul 2013 | 8 comments
QuietLeni's picture
2 Agree
0 Disagree
+2 2 Votes
Login to vote

Dear Symantec,

I wanted to use the Workflow SQL Query/Script Generator to run with a similar script to this:

DECLARE @SQL VARCHAR(1024)

SET @SQL = 'SELECT * FROM vComputer WHERE Name LIKE ''' + @Name + ''''

EXEC (@SQL)

In this script, @SQL needs to be an internal variable for the script and @Name needs to be the Input Parameter. The resulting query, of course, will be the output parameter.

When I run the script in the Query/Script Generator, it takes BOTH @Name and SQL as parameters and therefore, @SQL can be written to from the user. There is no way of telling the Generator that @SQL is not to be touched. I have tried setting "Db parameter" to false and setting @SQL to be nullable.

I still want @Name to be an input parameter, but can we please be able to set a variable as being "internal" or just not include it in the list of parameters, please? This would also allow me to create more readable queries, rather than having to make them run in more than one line.

Kindest regards,

QuietLeni

 

Comments 8 CommentsJump to latest comment

reecardo's picture

Just curious... why do you need to do dynamic SQL here? You can just specify

SELECT * FROM vComputer WHERE Name LIKE @Name

as the SQL to run

Unless you plan on doing something sneaky like this:

DECLARE @SQL VARCHAR(1024)

SET @SQL = 'SELECT * FROM vComputer WHERE Name IN (' + @Name + ')' --Name is a comma delimited set of strings

EXEC (@SQL)

Then you WOULD need some dynamic SQL mechanism... if this is the case, I don't see a route to do it with Query/Script generator. You CAN however, hoist the code to a stored procedure (where the SP builds dynamic SQL and runs) and then use the stored procedure generator.

Hope this helps.

 

0
Login to vote
QuietLeni's picture

reecardo,

Actually, the example I gave is a pretty terrible (but much more readable) example of Dynamic SQL and VERY simplified code. Your example is much better than mine.

It is just the problem that, without Stored Procedures, you cannot do this sort of thing in the Query/Script generator.

The exact (and horribly unreadable) example that I was trying to do was:

DECLARE @IncludesSQL VARCHAR(1024)

SET @Includes = '0001,0002,0003'

SET @IncludesSQL = 'WHERE ((Name IS LIKE ''%' + REPLACE(@Includes, ',', '%'') AND (Name IS LIKE ''%') + ')'

EXEC ('SELECT * FROM vComputer' + @IncludesSQL)

As you quite rightly guessed, I wanted to poke the parameter of @Includes into the generator and then for it to work out the rest.

Problem with SPs is that you need to be able to write SPs in the database (and you sometimes do not have that access).

This does, however, highlight the need to be able to create "internal" variables that are not parameters, such as in the SQL:

DECLARE @InputParameter INT

IF (@InputParameter > 5 AND @InputParameter < 10)

BEGIN

         SET @SpeedCheck = 1024

END

ELSE

BEGIN

        SET @SpeedCheck = 2048

END

SELECT * FROM Inv_CPU_Speed

WHERE Speed > @SpeedCheck

This is not dynamic, but still shows the need for such "internal variables" in the Query/Script Generator.

What is the point of an Asset Management Solution that needs excessive management? Let me help you.

0
Login to vote
QuietLeni's picture

Actually, I think that I could structure that code to be Query/Script Generator-friendly:

SELECT * FROM Inv_CPU_Speed

WHERE ((@InputParameter > 5 AND @InputParameter < 10) AND (Speed > 1024))

OR ((@InputParameter < 5 OR @InputParameter > 10) AND (Speed > 2048))

I bet that, given the time, I could come up with another reason to use internal variables!

What is the point of an Asset Management Solution that needs excessive management? Let me help you.

0
Login to vote
michael.george's picture

Regardeless of your need for them, though I tend to think that not being able to add a stored procedure or trigger is the most likely scenario, you can definitely use internal variables. I've done it a few times in 7.1 and in the pre-release 7.5 that ships with ServiceDesk.

When the generator looks for parameters, it looks for any variable within the SQL code. My solution has been to give it a list of parmeters it will need (SELECT @var1, @var2) and then click the button. Once that's done, I put in the code I actually want to use and it does not recheck for parameters unless you click the button again.

BTW, this is not a bug Symantec, so don't 'fix' it for me ;)

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

+2
Login to vote
QuietLeni's picture

Michael,

*THAT* is SNEAKY!!!! Thanks for that! I still think that the process should have an option of "Ignore" or to delete a "found" parameter.

What is the point of an Asset Management Solution that needs excessive management? Let me help you.

+1
Login to vote
michael.george's picture

Yes, it should. Make it an idea and I'll definitely up vote it! Still, this will work in the meantime.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

0
Login to vote
QuietLeni's picture

Well... why not upvote this one, Michael? It is saying this!!!!

What is the point of an Asset Management Solution that needs excessive management? Let me help you.

0
Login to vote
michael.george's picture

Yeah, somehow I missed that this was in the ideas and not the normal forums. I'm totally on the ball.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

0
Login to vote