Post reply

Writing SQL that prompts for values

  • 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

  • December 11, 2009 at 10:07 am

    #1091287

    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

  • December 11, 2009 at 11:02 am

    #1091318

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

    Best Regards,

    ~David

  • December 11, 2009 at 12:21 pm

    #1091352

    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

  • December 11, 2009 at 12:26 pm

    #1091354

    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

  • December 11, 2009 at 12:45 pm

    #1091364

    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_id integer not null,

    outlet_id nvarchar(20) not null,

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

    outlet_desc nvarchar(40)

    )

    go

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

    Best Regards,

    ~David

  • December 11, 2009 at 12:59 pm

    #1091375

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

  • December 11, 2009 at 1:04 pm

    #1091378

    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

  • December 11, 2009 at 1:05 pm

    #1091379

    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

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

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