Declare a concatenated value. - SQL Server Forums

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)

Declare a concatenated value.

Author

Topic

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-05 : 16:54:41

OMG i'm so stupid, i edited my original post instead of replying!!

I was wondering if there was away to write a stored procedure where I concatenate several columns to create a Phrase and use that Phrase as a new value to do a second search in another table.

Example.

Table 1.

Column1 (Modem) Column2 (Highspeed) Column3 (Black)

Concatenated to be MODEM HIGHSPEED BLACK.

The 2nd table has the concatenated value as it's own column and 2nd column for the number in Inventory.

Column1 (MODEM HIGHSPEED BLACK) Column2 (44)

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-05 : 17:09:39

May be something like this?


DECLARE @TableA Table (col1 varchar(30), col2 varchar(30),col3 varchar(30))
INSERT INTO @TableA Values('BLUE','HIGHSPEED','MODEM') 
INSERT INTO @TableA Values ('RED','HIGHSPEED','MODEM')
SELECT * FROM @TableA

DECLARE @TableB Table (col1 varchar(100),col2 varchar(100))
INSERT INTO @TableB Values('BLUE HIGHSPEED MODEM','BlueHighSpeed') 
INSERT INTO @TableB Values ('RED HIGHSPEED MODEM','RedHighSpeed')
SELECT * FROM @TableB

SELECT B.* FROM @TableB B
INNER JOIN @TableA A ON B.col1 = (A.Col1+' '+A.Col2+' '+A.Col3)

Thanks
Karunakaran

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-05 : 17:31:40

ooo i couldn't do it that way, there are over 3000 variations.

Here's what i'm thinking

CREATE PROCEDURE dbo.ArnoldsResults 

@TYPE (7)

AS
BEGIN

SELECT 
dbo.HARDWARE.TYPE + ' ' + dob.HARDWARE.SPEED+ dbo.HARDWARE.COLOUR
AS Part#
FROM dbo.HARDWARE
WHERE TYPE'%' +@TYPE + '%'

END


GO

Then whatever that outputs in the PART#

I want that to be the variable in the secondary search.

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-05 : 17:41:09

Can you please post the table structure and some sample data and the expected out put for that data.

Thanks
Karunakaran

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-05 : 18:17:31

This is a simplified version of it.

Table1 - Hardware

Type: VARCHAR (10)
Speed: VARCHAR (10)
Colour: VARCHAR (10)

Table 2 - Inventory

Product: VARCHAR (50) NOTE: This is not an automatically generated value, it just was just poor planning by whoever created this database that they typed it in as the equivalent of the concatenated values from Table1.

Availibility: INT

So the stored procedure will need to take the 3 columns from table1 combine them and use the combined value to select from table2.

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-05 : 18:38:50

Doesnt this the same as what you are looking for?

quote:


SELECT B.* FROM @TableB B
INNER JOIN @TableA A ON B.col1 = (A.Col1+' '+A.Col2+' '+A.Col3)

If you want the simplified stored procedure version of this.


CREATE PROCEDURE TestSp1
@TYPE (7)

AS
BEGIN

SELECT B.Availability,B.Product 
FROM 
Hardware A 
INNER JOIN Inventory B
ON 
B.Product =  (A.TYPE + ' ' + A.SPEED + ' ' + A.COLOUR)
WHERE A.TYPE LIKE '%' +@TYPE + '%'

END

Thanks
Karunakaran

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-05 : 18:41:54

Karuna's query does what you want you just need to replace the table and column names with those from your database.

SELECT 
	I.*
FROM
	Inventory AS I
INNER JOIN
	Hardware AS H
	ON I.Product = H.Type + ' ' + H.Speed + ' ' + H.Colour

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-05 : 18:43:22

You guys are amazing. Thank you!! I was analyzing it incorrectly, noob. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources