Writing SQL that prompts for values

Question

Writing SQL that prompts for values

David Kranes

SSCrazy

Points: 2523
More actions
December 11, 2009 at 9:46 am

#140610

Hello, I am trying to write a DDL script that will create database objects. In Oracle you can insert characters that will prompt for values during run time and substitute them. Is there a way to do this in T-SQL as well?
Here is what I am trying to do. I have the following computed column in one of my tables:
(left([outlet_id],(3)))
I would like to prompt for the second parameter in the left function. In this case it is 3.
Any help would be greatly appreciated.
Thank you,
David
Best Regards,
~David

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL is a server product. Typically it's running on a server and the client is elsewhere. To get a prompt for an input, you need a front end that has that capability. SQL Server just takes requests, processes them and returns the results. There's no interactivity involved.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Kranes SSCrazy Points: 2523 More actions · Answer 2

Gail, in Oracle it is built into SQL*Plus. I guess SQLCMD does not provide that functionality.

Best Regards,

~David

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

How about putting your query into a proc that requires an input parameter?

Then you could do something along the lines of

select LEFT(@string,@left)

Inside the proc.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GSquared SSC Guru Points: 260824 More actions · Answer 4

David Kranes (12/11/2009)
Gail, in Oracle it is built into SQL*Plus. I guess SQLCMD does not provide that functionality.

SQLCMD does. Check out http://www.databasejournal.com/features/mssql/article.php/3566401/Using-SQL-Server-2005-sqlcmd-Utility.htm

You asked about DDL scripts in T-SQL. That's a different subject from SQLCMD.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

David Kranes SSCrazy Points: 2523 More actions · Answer 5

Okay, this could be the answer if I can get the correct syntax. Here is the table DDL but it is giving an error:

create table outlet

(

retailer_idintegernot null,

outlet_idnvarchar(20)not null,

group_id AS (left([outlet_id],($(CharCnt)))) PERSISTED NOT NULL,

outlet_descnvarchar(40)

)

go

I want to pass in the $CharCnt using the -v sqlcmd option. Is this doable?

Best Regards,

~David

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Okay, so it gives an error. Care to share what that might be?

David Kranes SSCrazy Points: 2523 More actions · Answer 7

Sorry, I thought it was a syntax error as I stated above, but it was really a sqlcmd -v usage error. It now works. Thank you all for your help!

Best Regards,

~David

GSquared SSC Guru Points: 260824 More actions · Answer 8

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon