query in stored procedure help

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)

query in stored procedure help

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-16 : 14:51:34

I want to select state, city, and locname as it is in the database and I get invalid column because i am selecting it into one field and passing to function.. how can I get do this?


(
@param1 nvarchar(max)
)
as 
SET @param1 =(SELECT [QDINC\CFontan].[BOA_CleanString](@param1))
SELECT hits, DBString,  ID
FROM
(
SELECT [QDINC\CFontan].[BOA_Compare](@param1, val1) as hits, val1 as DBstring, ID
FROM
(SELECT [QDINC\CFontan].[BOA_CleanString]([locName] + '' + [City] + '' + [State]) AS val1, [uID] AS ID FROM dbo.tblLanxLocs)t
)r
WHERE hits > 30 
ORDER BY HITS DESC

Return @@ROWCOUNT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 14:57:07

[code](
@param1 nvarchar(max)
)
AS

SET NOCOUNT ON

SET @param1 = [QDINC\CFontan].[BOA_CleanString](@param1)

SELECT hits,
DBString,
ID
FROM (
SELECT [QDINC\CFontan].[BOA_Compare](@param1, val1) as hits,
val1 as DBstring,
ID
FROM (
SELECT [QDINC\CFontan].[BOA_CleanString]([locName] + '' + [City] + '' + [State]) AS val1,
[uID] AS ID
FROM dbo.tblLanxLocs
) AS t
) AS r
WHERE hits > 30
ORDER BY hits DESC

RETURN @@ROWCOUNT[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-16 : 15:01:27

what does below return?



SELECT [QDINC\CFontan].[BOA_Compare](@param1, val1) as hits, val1 as DBstring, ID
FROM
(SELECT [QDINC\CFontan].[BOA_CleanString]([locName] + '' + [City] + '' + [State]) AS val1, [uID] AS ID FROM dbo.tblLanxLocs
)t

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-18 : 17:12:02

It returns hits | dbstring | ID
0 | CRYCMPNYDDSNL | 1 --example row

hits int
dbstring nvarchar
ID int

quote:

Originally posted by visakh16

what does below return?



SELECT [QDINC\CFontan].[BOA_Compare](@param1, val1) as hits, val1 as DBstring, ID
FROM
(SELECT [QDINC\CFontan].[BOA_CleanString]([locName] + '' + [City] + '' + [State]) AS val1, [uID] AS ID FROM dbo.tblLanxLocs
)t

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-23 : 15:50:10

bump - can anyone help me select the city state, name field ?

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-06-23 : 15:53:06

evermind I got it

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources