PHISSUG

Hello guys

 I want to know if possible yung ganitong procedure

==========================================

CREATE PROCEDURE sprTest @Field as char(4)

AS

BEGIN

    Select @Field From TestTable

END

========================================

I have tried it and the output is yung pinasa kong parameter value at hindi yung data nung column na pinasa ko

We are using SQL 2000 SP3

Any help will be very much appreciated. =)

 

hi mansky,

the only solution to accomplish this is using dynamic sql.

 

CREATE PROCEDURE sprTest

    @Field as sysname

AS

BEGIN

    DECLARE @str as nvarchar(500)

   SET @str = 'Select ' + @Field  + 'From TestTable'

   execute @str

END

 

Arthur Jr:

hi mansky,

the only solution to accomplish this is using dynamic sql.

 

CREATE PROCEDURE sprTest

    @Field as sysname

AS

BEGIN

    DECLARE @str as nvarchar(500)

   SET @str = 'Select ' + @Field  + 'From TestTable'

   execute @str

END

 

watch out for SQL Injection on this.

yes, that is prone to sql injection. you need to validate your input before processing it. Here's a nice article on how to validate input. Thanks.

http://msdn.microsoft.com/en-us/library/ms161953.aspx

Hi. Thank you for the replies, I tried it but I still have one problem

What if my statement is:

Set @Str = 'Select ' + @Field + ' From Table Where Field2='A' '     <---- how can I put a single quote for my condition?

I tried this but it cannot execute my where statement

   

try this one...

USE Northwind -- I'm using Northwind Database of SQL 2000 

CREATE PROC Test @Field nvarchar(30)
AS
DECLARE @Str nvarchar(100)
Set @Str = 'Select ' + @Field + ' From Products Where ProductName =''Chai'' '  -- Use two single quotes( ' ' ) to include the single quotes in your                                                                                                                     -- dynamic query
EXECUTE(@Str)

/*  ========================================================= */

Exec Test 'ProductName'

 

 

 

 

---

MCP.MCAD.MCDBA
MCTS:SQL Server 2005
MCTS: Business Intelligence
MCITP:SQL Server 2005 Database Administrator
MCITP: Business Intelligence Applications Developer
Microsoft Certified Trainer
Database Administrator: Trend Micro

Gumana cya. What if in this case?

===========================================================

CREATE PROC Test @Field nvarchar(30)
AS
DECLARE @Str nvarchar(100), @Value nvarchar(100)
Set @Str = 'Select @Value=' + @Field + ' From Products Where ProductName =''Chai'' '  -- Use two single quotes( ' ' ) to include the single quotes in your                                                                                                                     -- dynamic query
EXECUTE(@Str)

/*  ========================================================= */

Exec Test 'ProductName'

=================================================================

I tried this one but it returns saying @Value variable must be declared.

Do you think may solution pa itong case na ito?

 

Tnx = )

There  might be solution for this but messy and complicated at it's best. The presence of a placeholder in the SELECT statement makes me believe that you are expecting a single record from this query and that the ProductName must be unique. Usually, based on my experience, SQL Server warns you about this kind of query. It tells you never to mix data retrieval routine in a select statement. I think this is the case if the criteria in the WHERE statement is not unique.

 Anyways, you're trying to create a very "flexible" query; one which let's you specify any column  to be retrieved. Honestly, this is very unconventional and I don't see any compelling reason to do this in production. You're better off fetching the entire row and filter the columns in the front end. It may not be efficient for your need but hey, it's just one row! It wouldn't hurt and it would save you a lot of hassle. Just my two cents.

you can create a temp table and dump the resultset return by that dynamic sql to that temp table

CREATE TABLE #temp(FieldValue varchar(250))

INERT INTO #temp EXECUTE('SELECT UserNameFROM UserTable')

SELECT * FROM #temp

 

but this make your life complicated. refrain from using dynamic sql as much as possible.

Arthur Jr:

you can create a temp table and dump the resultset return by that dynamic sql to that temp table

CREATE TABLE #temp(FieldValue varchar(250))

INERT INTO #temp EXECUTE('SELECT UserNameFROM UserTable')

SELECT * FROM #temp

 

but this make your life complicated. refrain from using dynamic sql as much as possible.

I will try this one.

The reason why I am doing this is because I have an application that will be deploy to 3 countries. I have a table that holds all text string of label names, form names, returned messages,etc. So I create 3 fields for the 3 countries. So in my front-end application, I will just set what country it will use. When my stored procedure need to return a message, it should be by the set country text string. The column definition in this table is ITEM1,ITEM2,COUNTRY1,COUNTRY2,COUNTRY3. That is why I want to use the placeholder so that when I need to get a value by country, I don't need to code the condition in my stored procedure for each country, that makes my stored procedure long. Anyway, applciation flexibility is my goal here.

=)

 

The column definition in this table is ITEM1,ITEM2,COUNTRY1,COUNTRY2,COUNTRY3. That is why I want to use the placeholder so that when I need to get a value by country, I don't need to code the condition in my stored procedure for each country, that makes my stored procedure long. Anyway, applciation flexibility is my goal here.

=)

Interesting Mansky. Just wondering though; why not go for the heirarchical approach, normalize your table and break that to two: Country and CountryString? CountryString has an FK from Country. Less maintenance since you don't have to add another column when you include a new country. Best of all, you conform to best practice of avoiding dynamic queries.

 

twistur:

 

The column definition in this table is ITEM1,ITEM2,COUNTRY1,COUNTRY2,COUNTRY3. That is why I want to use the placeholder so that when I need to get a value by country, I don't need to code the condition in my stored procedure for each country, that makes my stored procedure long. Anyway, applciation flexibility is my goal here.

=)

Interesting Mansky. Just wondering though; why not go for the heirarchical approach, normalize your table and break that to two: Country and CountryString? CountryString has an FK from Country. Less maintenance since you don't have to add another column when you include a new country. Best of all, you conform to best practice of avoiding dynamic queries.

I agree with Twistur. The situation you wanted to achieve can be corrected by having a proper db design. Twistur's suggestion is more flexible.

Listen to twistur, that is the correct approach. That will save your from trouble of writing verrrrrrrrrrrrrrrrrrrrrrry complexxxxxxxxxxxx sql statement.hehehe

I also thought of it in that method before but I think our solution is simpler but now the it is getting complexed with regards to flexibility in data gathering. Anyway, we will talk about it if we will transform it to Twistur's solution.

Thank you to all.

=)

select
    ITEM1,
    ITEM2,
        CASE @field
            WHEN 'COUNTRY1' THEN COUNTRY1
            WHEN 'COUNTRY2' THEN COUNTRY2
            WHEN 'COUNTRY3' THEN COUNTRY3
            ELSE COUNTRY_ELSE       
        END
    as Country
FROM table_1

 

How do you think about this one..

Pasesenya na kung may mali sa syntax. novice pa ako sa sql server.. pero  eto ung idea ko.