how to pass values single quotes on query ,

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to pass values single quotes on query ,

Author

Topic

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-07 : 05:58:04

HI,
I want to pick countries from one of table but i want Canada,United Kingdom and United State at the top and i wrote the query like this and it is working fine.

select Top (3) * from tbl_countries where countryName in('UNITED STATES','UNITED KINGDOM','CANADA')
Union
Select * from tbl_Countries where countryName not in('UNITED STATES','UNITED KINGDOM','CANADA')

but now i want to change this to an Store procedure where if i pass the name of the countries which i want at the top and top number like here (3),so how can i make this as generic so that if next time i pass n countries names that must come on top,
I tested the Storeprocedure like
GetAllCountriesWithTop 'UNITED STATES','UNITED KINGDOM',CANADA',3

it is giving error
Procedure or function GetAllCountriesWithTop has too many arguments specified.
as i know i have only two parameters ,so how can i pass the countries with single code so that i can directly use in the in clause of query or any other logic.

Thanks In advance

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-07 : 06:04:41

Pass country names as a single comma-separated string and inside SP, make use of table-valued function
to parse the string and return table of country names, which then you can use in your SP.

EXEC GetAllCountriesWithTop '''UNITED STATES'',''UNITED KINGDOM'',''CANADA''',3

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-07 : 06:09:10

quote:
Originally posted by harsh_athalye

Pass country names as a single comma-separated string and inside SP, make use of table-valued function
to parse the string and return table of country names, which then you can use in your SP.

EXEC GetAllCountriesWithTop '''UNITED STATES'',''UNITED KINGDOM'',''CANADA''',3

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I want to pass countries with single parameter only because currently we have 247 countries name in table and tomorrow they may ask 5 or 10 countries on top so I dont want to change sp on every country addition.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-07-07 : 06:17:30

I think you haven't read my reply carefully. I have told you exactly that.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 06:27:47

quote:
Originally posted by rafeequddin_ahmed

quote:
Originally posted by harsh_athalye

Pass country names as a single comma-separated string and inside SP, make use of table-valued function
to parse the string and return table of country names, which then you can use in your SP.

EXEC GetAllCountriesWithTop '''UNITED STATES'',''UNITED KINGDOM'',''CANADA''',3

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I want to pass countries with single parameter only because currently we have 247 countries name in table and tomorrow they may ask 5 or 10 countries on top so I dont want to change sp on every country addition.

the quotes in between are for escaping main quotes and not to consider it as a new param.so its exactly what you asked for. also try this to understand how quote works

SELECT ''','''',''''',''''''

rafeequddin_ahmed
Starting Member

23 Posts

Posted - 2008-07-07 : 07:00:57

quote:
Originally posted by visakh16

quote:
Originally posted by rafeequddin_ahmed

quote:
Originally posted by harsh_athalye

Pass country names as a single comma-separated string and inside SP, make use of table-valued function
to parse the string and return table of country names, which then you can use in your SP.

EXEC GetAllCountriesWithTop '''UNITED STATES'',''UNITED KINGDOM'',''CANADA''',3

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I want to pass countries with single parameter only because currently we have 247 countries name in table and tomorrow they may ask 5 or 10 countries on top so I dont want to change sp on every country addition.

the quotes in between are for escaping main quotes and not to consider it as a new param.so its exactly what you asked for. also try this to understand how quote works

SELECT ''','''',''''',''''''

yep it is working,but as I can not use use what i m passing as countryname for in clause.so i wrote dynamic query ,it is working for me,
thanks Harsh Athalye

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 13:53:16

quote:
Originally posted by rafeequddin_ahmed

quote:
Originally posted by visakh16

quote:
Originally posted by rafeequddin_ahmed

quote:
Originally posted by harsh_athalye

Pass country names as a single comma-separated string and inside SP, make use of table-valued function
to parse the string and return table of country names, which then you can use in your SP.

EXEC GetAllCountriesWithTop '''UNITED STATES'',''UNITED KINGDOM'',''CANADA''',3

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I want to pass countries with single parameter only because currently we have 247 countries name in table and tomorrow they may ask 5 or 10 countries on top so I dont want to change sp on every country addition.

the quotes in between are for escaping main quotes and not to consider it as a new param.so its exactly what you asked for. also try this to understand how quote works

SELECT ''','''',''''',''''''

yep it is working,but as I can not use use what i m passing as countryname for in clause.so i wrote dynamic query ,it is working for me,
thanks Harsh Athalye

then just pass them as

EXEC GetAllCountriesWithTop 'UNITED STATES,UNITED KINGDOM,CANADA',3

and in sp fileter like this

and ',' + @Country + ',' LIKE '%,' + YourCountryField + ',%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources