Can a script prompt for a value to be inputed?

Hello all,
Is there any way to get a script to prompt for a value (input box or
something else) or to use a value from a variable using SQLCMD?
I have these SELECT scripts that are essentially Crosstab/Pivot queries
where each column is a month of data and is created by a CASE statement.
When updates are done, the CASE statements for the new months need to be
created.
We want to automate this process for a client so they can have one of their
non-DBA employees do the work.
Now I have come up with some Dynamic SQL that will create the same script
and run it based on just a start date and an end date. Start date will be
fixed, the end date will change from update to update. Using the Dynamic
SQL script is alot easier--all we need to do is change one single end date,
rather than adding several CASE statements to the non-Dynamic SQL script.
What I would like to do is create a Dynamic SQL script that will prompt for
an end date to be entered. Now I have figured out how to get SQLCMD to run
a script file and save results to a file, so if I could get the Dynamic SQL
script to accept a variable from SQLCMD to set the end date, that would work
too.
Thanks for any help anyone can provide,
Conan KellyEnd users, dynamic crosstab queries...have you heard of Rac? :)
www.rac4sql.net
We did the dirty work, you take the credit.
www.beyondsql.blogspot.com
Putting logic back in application development|||There's no prompting in the SQL Server tools. But SQLCMD accepts a variable (though the -v switch
and $(varname) literal inside the script file).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is there any way to get a script to prompt for a value (input box or something else) or to use a
> value from a variable using SQLCMD?
> I have these SELECT scripts that are essentially Crosstab/Pivot queries where each column is a
> month of data and is created by a CASE statement. When updates are done, the CASE statements for
> the new months need to be created.
> We want to automate this process for a client so they can have one of their non-DBA employees do
> the work.
> Now I have come up with some Dynamic SQL that will create the same script and run it based on just
> a start date and an end date. Start date will be fixed, the end date will change from update to
> update. Using the Dynamic SQL script is alot easier--all we need to do is change one single end
> date, rather than adding several CASE statements to the non-Dynamic SQL script.
> What I would like to do is create a Dynamic SQL script that will prompt for an end date to be
> entered. Now I have figured out how to get SQLCMD to run a script file and save results to a
> file, so if I could get the Dynamic SQL script to accept a variable from SQLCMD to set the end
> date, that would work too.
> Thanks for any help anyone can provide,
> Conan Kelly
>|||Tibor,
Thank you for your help.
That works EXACTLY the way I want it to.
Thanks again,
Conan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23j$uliR8HHA.1168@.TK2MSFTNGP02.phx.gbl...
> There's no prompting in the SQL Server tools. But SQLCMD accepts a
> variable (though the -v switch and $(varname) literal inside the script
> file).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
> news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
>> Hello all,
>> Is there any way to get a script to prompt for a value (input box or
>> something else) or to use a value from a variable using SQLCMD?
>> I have these SELECT scripts that are essentially Crosstab/Pivot queries
>> where each column is a month of data and is created by a CASE statement.
>> When updates are done, the CASE statements for the new months need to be
>> created.
>> We want to automate this process for a client so they can have one of
>> their non-DBA employees do the work.
>> Now I have come up with some Dynamic SQL that will create the same script
>> and run it based on just a start date and an end date. Start date will
>> be fixed, the end date will change from update to update. Using the
>> Dynamic SQL script is alot easier--all we need to do is change one single
>> end date, rather than adding several CASE statements to the non-Dynamic
>> SQL script.
>> What I would like to do is create a Dynamic SQL script that will prompt
>> for an end date to be entered. Now I have figured out how to get SQLCMD
>> to run a script file and save results to a file, so if I could get the
>> Dynamic SQL script to accept a variable from SQLCMD to set the end date,
>> that would work too.
>> Thanks for any help anyone can provide,
>> Conan Kelly
>