Conditional Formatting on User Prompt result

I'm trying to format Sql procedure depending on user Prompt Result.

Basically Trying to ask user if he/she wants to drop existing procedure or rename.

If user chooses "Rename" Macro will add "1" or "2" or "3" to the end of the procedure name.

Pseudo Code would be like

Prompt Procedure name
if Procedure exists
Prompt user if her want to rename or continue

If user chooses Yes Enter insert Create Procedure sql with Number at the end.
Else just insert Create Procedure SQL.

Any help?

Please post what you have now and I will suggest how to correct it and make it work.

The situation is a bit more complex than just inserting CREATE PROCEDURE procname. I think the $PROMPT$ macro in snippets is evaluated before anything else so you cannot have a prompt to be shown (or not shown) based on a result of a query. Therefore you have to decide in advance IF you want to create a numbered version of the procedure in case there is one already existing by the given name. If that's what you want then the following snippet works on MSSQL Server 2k8 (it might require some adjustments for other DBs):

Code:

$PROMPT(pschema,Schema?,dbo)$
$PROMPT(ppname,Procedure name?)$
$PROMPT(renumber,Renumber in case it exists?,,,0 1,No Yes)$
CREATE PROCEDURE $pschema$.$$
SELECT
CASE
   WHEN $renumber$ = 0 THEN '$ppname$'
   WHEN NOT EXISTS (
         SELECT
            1
         FROM
            sys.objects AS o
         WHERE o.[name] = '$ppname$'
            AND o.[schema_id] = SCHEMA_ID('$pschema$')
            AND o.[type] = 'P'
      ) THEN '$ppname$'
   ELSE '$ppname$' + '_' + (
         SELECT
            CAST(COALESCE(MAX(SUBSTRING(o.[name] ,LEN('$ppname$') + 2 ,255)),1) + 1 AS VARCHAR(4))
         FROM
            sys.objects AS o
         WHERE o.[name] LIKE '$ppname$' + '_%'
            AND ISNUMERIC(SUBSTRING(o.[name] ,LEN('$ppname$') + 2 ,255)) = 1
            AND o.[type] = 'P'
      )
END
$$

The problem with this approach is that the generated code is pretty much static, meaning that you can run it once but it will definitely fail if you try to run it again, since the name numbering is not updated. If you need that then you have to create dynamic sql to create the procedure in the first place. Cumbersome to edit and might not worth the effort.

Just to add my two cents to the topic, I think that sequentially numbering database objects is a flaw in database design. We've been doing that and I still have to kick some b#tts nowadays because of it. It makes a mess in the database and documentation becomes a real nightmare. I recommend not doing it.

Pretty cool! Thank you

Just in case, it is likely possible to make the snippet create procedure dynamically or output CREATE PROCEDURE code using SQL statements executed after the $PROMPT$ macro. The snippet type should be set to "Execute and Display Output Results" and virtually all actions including checking for existing procedure would need to go to the SQL portion of the snippet between $$...$$ marks. That's likely doable but not a trivial task.

Last edited by SysOp on Wed Apr 11, 2012 10:15 am; edited 1 time in total

Exactly. It is doable but certainly not an easy task. That's why I said it's cumbersome. It's hard to maintain the generated dynamic sql (it is a pure string, no syntax check, no syntax highlight, a horror :) You will eventually fall back to the script from the database itself and either change the number manually, cut the CREATE PROCEDURE part of it or create a snippet which does this for you. Or you have to save the original source, reload it whenever you want to change it (if you're lucky it has not been changed by somebody else) and use a snippet that transforms it into dynamic procedure creator. In my opinion, a very high price paid for the small convenience of not having to renumber manually.

Thanks gemisigo

	gemisigo wrote:
	Or you have to save the original source, reload it whenever you want to change it (if you're lucky it has not been changed by somebody else)

Does anyone have the rights to change it?

Last edited by justhuds on Fri May 04, 2012 10:27 am; edited 1 time in total

	Quote:
	Does anyone have the rights to change it?

I'm sorry I don't get your question. Change what?

In response to "justhuds"
This is a managed support forum. All inappropriate posts will be deleted. A second attempt to post inappropriate message will lead to disabling of your account.

What happened???

That was a response to user justhuds. Please disregard any email notifications that response and previous messages may have triggered.