November 25, 2013 at 4:40 am
Hii guys,
I facing a problem in comparing the rows of the table to the Type table
here is the detailed information regarding my table
Country_lkp
CREATE TABLE [dbo].[Country_lkp](
[CountryCode] [char](3) NOT NULL,
[CountryName] [varchar](50) NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [Pk_Country_lkp] PRIMARY KEY CLUSTERED
(
[CountryCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
countryid
CREATE TABLE [dbo].[countryid](
[autoid] [int] IDENTITY(1,1) NOT NULL,
[listid] [varchar](50) NULL
) ON [PRIMARY]
GO
Userdefined Type(TVP)
CREATE TYPE [dbo].[Country] AS TABLE(
[CountryCode] [char](3) NULL,
[CountryName] [varchar](50) NULL
)
GO
Procedure
My Procedure goes like this my requirement is to select the id of the list of countries that are inserted into the type table
Create PROCEDURE [dbo].[Insert_Country_lkp]
(
@Countrylist AS Country READONLY
)AS
declare
@Count int,
@i int=0,
@id int,
@idlist varchar(50)
BEGIN
INSERT INTO Country_lkp (CountryCode,CountryName) SELECT * FROM @Countrylist
SELECT @count=COUNT(*) FROM @Countrylist
while(@i<=@Count)
SELECT @id=id
FROm Country_lkp AS soh
JOIN @Countrylist AS cl ON
cl.CountryCode = soh.CountryCode and cl.CountryName=soh.CountryName;
set @idlist=@id+','
insert into countryid values (@idlist)
set @i+=1
END
Am not getting the exact logic regarding how to compare the rows of the tvp to the database table kindly help me out to get the solution
Regards
Surya
November 25, 2013 at 8:04 am
Excellent job posting ddl. What I don't quite understand is where the issue is. I would recommend going away from the type of storage you are using with countryid. You are storing multiple values in a single column. This violates 1NF. It also makes your code a lot more difficult to deal with. Even if you stick with this architecture you do not need a loop to accomplish this. Since all this is doing is generating a comma separated list you should look at this article. http://www.sqlservercentral.com/articles/71700/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2013 at 8:25 am
Actually my problem was how to compare the row data in the Type(TVP) with the row data of my table country_lkp
November 25, 2013 at 8:27 am
Trainee SQL (11/25/2013)
Actually my problem was how to compare the row data in the Type(TVP) with the row data of my table country_lkp
I don't see anything wrong with what you are doing regarding that. Is is not doing something correctly?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2013 at 9:48 am
Yeah in fact the code isn't gives me any progress could you help me in single code regarding how to compare the Tvp data with table data
November 25, 2013 at 9:56 am
Trainee SQL (11/25/2013)
Yeah in fact the code isn't gives me any progress could you help me in single code regarding how to compare the Tvp data with table data
user defined table types behave exactly like any normal table. What exactly do you mean by help with comparing? I am willing and able to help you but I don't know what you are having a problem doing here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2013 at 1:23 pm
I'm not sure why you would want to insert all iterations of the list of values into the countryid table, but this should fix a couple of the potential errors in the code:
Create PROCEDURE [dbo].[Insert_Country_lkp]
(
@Countrylist AS Country READONLY
)AS
declare
@Count int,
@i int=0,
@id int,
@idlist varchar(50)
SET @idlist = ''
BEGIN
INSERT INTO Country_lkp (CountryCode,CountryName) SELECT * FROM @Countrylist
SELECT @count=COUNT(*) FROM @Countrylist
while(@i<=@Count)
SELECT @id=id
FROm Country_lkp AS soh
JOIN @Countrylist AS cl ON
cl.CountryCode = soh.CountryCode;
set @idlist=CAST(@id AS varchar(10))+','
set @i+=1
insert into countryid values (@idlist) --??for every iteration??
END --WHILE
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply