Stored Procedure parameter collation

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure parameter collation

Author

Topic

sumantakundu
Starting Member

6 Posts

Posted - 2010-10-19 : 09:41:18

Hi
I recently got a SQL stored procedure from my head office for a report in SSRS, after executing the SP the report gace collation error.

We found that the Stored procedure is using parameter using the collation below:
@as_property varchar 6 6 NULL 1 SQL_Latin1_General_CP1_CI_AS

However my database collation is Latin1_General_CI_AS

I have no clue on how can I chnage the collation in the stored procedure. Some website metion that to drop the SP and recreated it but no luck.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 12:47:28

in comparisons using parameter use

@as_property COLLATE database_default

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:52:26

show your full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 14:51:36

Visakh told you to use COLLATE database_default in comparisons!
Means in WHERE or in ON clause.
Where column = @as_property COLLATE database_default for example

No, you're never too old to Yak'n'Roll if you're too young to die.

sumantakundu
Starting Member

6 Posts

Posted - 2010-10-20 : 01:02:35

Hi visakh,

I am not good is SQL but here is the Full query.

The current collation of the DB is Latin1_General_CI_AS

I need this SP to follow the DB collation.

/****** Object: Stored Procedure dbo.sp_sp010 Script Date: 24/Mar/2008 10:42 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_sp010') and sysstat & 0xf = 4)
drop procedure dbo.sp_sp010
GO

CREATE PROCEDURE sp_sp010
@as_property VARCHAR(6) ,
@adt_from DATETIME,
@adt_to DATETIME,
@as_ghacct VARCHAR(12),
@as_history_code VARCHAR(6),
@as_manager VARCHAR(6),
@as_group VARCHAR(7),
@as_type VARCHAR(6),
@ai_top INTEGER ,
@as_no_prod VARCHAR(6)

WITH ENCRYPTION AS
DECLARE @ls_chacct VARCHAR(12)
DECLARE @li_count INTEGER
DECLARE @ls_hostprefix VARCHAR(4)

-- Create Temp table #sp010
CREATE TABLE #sp010 (
chacct VARCHAR (12) NULL,
source VARCHAR (6) NULL,
nights INTEGER NULL,
cancel INTEGER NULL,
noshow INTEGER NULL,
roomrevenue NUMERIC(15, 2) NULL,
otherrevenue NUMERIC(15, 2) NULL
)

-- Create Temp table #sp010_top
CREATE TABLE #sp010_top (
grouping VARCHAR (6) NULL,
chacct VARCHAR (12) NULL
)

-- Create Temp table #sp010_final
CREATE TABLE #sp010_final (
grouping VARCHAR (6) NULL,
chacct VARCHAR (12) NULL,
--JB09122003 name VARCHAR (30) NULL,
name VARCHAR (70) NULL,
source VARCHAR (6) NULL,
description VARCHAR (30) NULL,
resv INTEGER NULL,
nights INTEGER NULL,
cancel INTEGER NULL,
noshow INTEGER NULL,
roomrevenue NUMERIC(15, 2) NULL,
otherrevenue NUMERIC(15, 2) NULL
)

-- Build production information
INSERT INTO #sp010
SELECT g.chacct,
g.source,
ISNULL (SUM (CASE WHEN g.status IN ('I', 'O') THEN ( DateDiff(d, (Case When @adt_from >= g.arrival Then @adt_from Else g.arrival End),
(Case When @adt_to <= g.departure Then @adt_to Else g.departure End)) +
Case When g.departure > @adt_to Then 1 Else 0 END )
ELSE 0 END), 0) 'nights',
ISNULL (SUM (CASE WHEN g.status='C' THEN ( DateDiff(d, (Case When @adt_from >= g.arrival Then @adt_from Else g.arrival End),
(Case When @adt_to <= g.departure Then @adt_to Else g.departure End)) +
Case When g.departure > @adt_to Then 1 Else 0 END )
ELSE 0 END), 0) 'cancel',
ISNULL (SUM (CASE WHEN g.status='N' THEN ( DateDiff(d, (Case When @adt_from >= g.arrival Then @adt_from Else g.arrival End),
(Case When @adt_to <= g.departure Then @adt_to Else g.departure End)) +
Case When g.departure > @adt_to Then 1 Else 0 END )
ELSE 0 END), 0) 'noshow',
0 'roomrevenue',
0 'otherrevenue'
FROM guest g (nolock), history h (nolock)
WHERE g.chacct = h.ghacct AND
( ( DateAdd(dd, -1, g.departure) >= @adt_from AND g.arrival <= @adt_from ) OR
( g.arrival <= @adt_to AND DateAdd(dd, -1, g.departure) >= @adt_to ) OR
( g.arrival >= @adt_from AND DateAdd(dd, -1, g.departure) <= @adt_to ) OR
( g.arrival <= @adt_from AND DateAdd(dd, -1, g.departure) >= @adt_to ) ) AND
g.chacct IS NOT NULL AND
g.shareacct IS NULL AND
g.status IN ('I', 'O', 'C', 'N') AND
g.property like @as_property AND
(h.ghacct = @as_ghacct OR @as_ghacct = '%') AND
(h.history_code = @as_history_code OR @as_history_code = '%') AND
(h.manager = @as_manager OR @as_manager = '%')
GROUP BY g.chacct, g.source
UNION ALL
SELECT g.chacct,
g.source,
( DateDiff(d, (Case When @adt_from >= min(g.arrival) Then @adt_from Else min(g.arrival) End),
(Case When @adt_to <= max(g.departure) Then @adt_to Else max(g.departure) End)) +
Case When max(g.departure) > @adt_to Then 1 Else 0 END ) 'nights',
0 'cancel',
0 'noshow',
0 'roomrevenue',
0 'otherrevenue'
FROM guest g (nolock), history h (nolock)
WHERE g.chacct = h.ghacct AND
( ( DateAdd(dd, -1, g.departure) >= @adt_from AND g.arrival <= @adt_from ) OR
( g.arrival <= @adt_to AND DateAdd(dd, -1, g.departure) >= @adt_to ) OR
( g.arrival >= @adt_from AND DateAdd(dd, -1, g.departure) <= @adt_to ) OR
( g.arrival <= @adt_from AND DateAdd(dd, -1, g.departure) >= @adt_to ) ) AND
g.chacct IS NOT NULL AND
g.shareacct IS NOT NULL AND
g.status IN ('I', 'O') AND
g.property like @as_property AND
(h.ghacct = @as_ghacct OR @as_ghacct = '%') AND
(h.history_code = @as_history_code OR @as_history_code = '%') AND
(h.manager = @as_manager OR @as_manager = '%')
GROUP BY g.chacct, g.source, g.shareacct
UNION ALL
SELECT g.chacct,
g.source,
0 'nights',
( DateDiff(d, (Case When @adt_from >= min(g.arrival) Then @adt_from Else min(g.arrival) End),
(Case When @adt_to <= max(g.departure) Then @adt_to Else max(g.departure) End)) +
Case When max(g.departure) > @adt_to Then 1 Else 0 END ) 'cancel',
0 'noshow',
0 'roomrevenue',
0 'otherrevenue'
FROM guest g (nolock), history h (nolock)
WHERE g.chacct = h.ghacct AND
( ( DateAdd(dd, -1, g.departure) >= @adt_from AND g.arrival <= @adt_from ) OR
( g.arrival <= @adt_to AND DateAdd(dd, -1, g.departure) >= @adt_to ) OR
( g.arrival >= @adt_from AND DateAdd(dd, -1, g.departure) <= @adt_to ) OR
( g.arrival <= @adt_from AND DateAdd(dd, -1, g.departure) >= @adt_to ) ) AND
g.chacct IS NOT NULL AND
g.shareacct IS NOT NULL AND
g.status = 'C' AND
g.property like @as_property AND
(h.ghacct = @as_ghacct OR @as_ghacct = '%') AND
(h.history_code = @as_history_code OR @as_history_code = '%') AND
(h.manager = @as_manager OR @as_manager = '%')
GROUP BY g.chacct, g.source, g.shareacct
UNION ALL
SELECT g.chacct,
g.source,
0 'nights',
0 'cancel',
( DateDiff(d, (Case When @adt_from >= min(g.arrival) Then @adt_from Else min(g.arrival) End),
(Case When @adt_to <= max(g.departure) Then @adt_to Else max(g.departure) End)) +
Case When max(g.departure) > @adt_to Then 1 Else 0 END ) 'noshow',
0 'roomrevenue',
0 'otherrevenue'
FROM guest g (nolock), history h (nolock)
WHERE g.chacct = h.ghacct AND
( ( DateAdd(dd, -1, g.departure) >= @adt_from AND g.arrival <= @adt_from ) OR
( g.arrival <= @adt_to AND DateAdd(dd, -1, g.departure) >= @adt_to ) OR
( g.arrival >= @adt_from AND DateAdd(dd, -1, g.departure) <= @adt_to ) OR
( g.arrival <= @adt_from AND DateAdd(dd, -1, g.departure) >= @adt_to ) ) AND
g.chacct IS NOT NULL AND
g.shareacct IS NOT NULL AND
g.status = 'N' AND
g.property like @as_property AND
(h.ghacct = @as_ghacct OR @as_ghacct = '%') AND
(h.history_code = @as_history_code OR @as_history_code = '%') AND
(h.manager = @as_manager OR @as_manager = '%')
GROUP BY g.chacct, g.source, g.shareacct
UNION ALL
SELECT g.chacct,
g.source,
0 'nights',
0 'cancel',
0 'noshow',
ISNULL(sum(CASE WHEN z.revenue_type='R'
THEN t.amount ELSE 0 END), 0) 'roomrevenue',
ISNULL(sum(CASE WHEN z.revenue_type='O'
THEN t.amount ELSE 0 END), 0) 'otherrevenue'
FROM guest g (nolock), z_trancode z (nolock), transactions t (nolock), history h (nolock)
WHERE g.property = t.property AND
g.account = t.account AND
z.property = t.property AND
z.code = t.tran_code AND
z.revenue_type IN ('R', 'O') AND
g.chacct = h.ghacct AND
( t.post_date between @adt_from and @adt_to ) AND
g.chacct IS NOT NULL AND
g.status IN ('I', 'O') AND
g.property like @as_property AND
(h.ghacct = @as_ghacct OR @as_ghacct = '%') AND
(h.history_code = @as_history_code OR @as_history_code = '%') AND
(h.manager = @as_manager OR @as_manager = '%')
GROUP BY g.chacct, g.source
UNION ALL
SELECT g.chacct,
g.source,
0 'nights',
0 'cancel',
0 'noshow',
ISNULL(sum(CASE WHEN z.revenue_type='R'
THEN t.amount ELSE 0 END), 0) 'roomrevenue',
ISNULL(sum(CASE WHEN z.revenue_type='O'
THEN t.amount ELSE 0 END), 0) 'otherrevenue'
FROM guest g (nolock), z_trancode z (nolock), transactions t (nolock), history h (nolock)
WHERE g.property = t.property AND
g.account = t.account AND
z.property = t.property AND
z.code = t.tran_code AND
z.revenue_type IN ('R', 'O') AND
g.chacct = h.ghacct AND
t.post_date between @adt_from and @adt_to AND
g.chacct IS NOT NULL AND
g.status IN ('P', 'R', 'C', 'N') AND
g.property like @as_property AND
(h.ghacct = @as_ghacct OR @as_ghacct = '%') AND
(h.history_code = @as_history_code OR @as_history_code = '%') AND
(h.manager = @as_manager OR @as_manager = '%')
GROUP BY g.chacct, g.source

-- Top A/C
IF @as_group = 'TOP' and @as_no_prod <> 'Y'
BEGIN
-- By Room Nights
IF @as_type = 'RMNGT'
BEGIN
DECLARE cursor_top CURSOR FOR
SELECT chacct
FROM #sp010 (nolock)
GROUP BY chacct
ORDER BY SUM(nights) DESC
OPEN cursor_top
FETCH NEXT FROM cursor_top INTO @ls_chacct
SELECT @li_count = 0
WHILE @@FETCH_STATUS = 0 AND @li_count < @ai_top
BEGIN
SELECT @li_count = @li_count + 1
INSERT INTO #sp010_top VALUES (CONVERT(VARCHAR(6), @li_count), @ls_chacct)
FETCH NEXT FROM cursor_top INTO @ls_chacct
END
CLOSE cursor_top
DEALLOCATE cursor_top
END
ELSE
-- By Room Revenue
BEGIN
DECLARE cursor_top CURSOR FOR
SELECT chacct
FROM #sp010 (nolock)
GROUP BY chacct
ORDER BY SUM(roomrevenue) DESC
OPEN cursor_top
FETCH NEXT FROM cursor_top INTO @ls_chacct
SELECT @li_count = 0
WHILE @@FETCH_STATUS = 0 AND @li_count < @ai_top
BEGIN
SELECT @li_count = @li_count + 1
INSERT INTO #sp010_top VALUES (CONVERT(VARCHAR(6), @li_count), @ls_chacct)
FETCH NEXT FROM cursor_top INTO @ls_chacct
END
CLOSE cursor_top
DEALLOCATE cursor_top
END

-- Write into the final table
INSERT INTO #sp010_final
SELECT t.grouping,
t.chacct,
h.name,
p.source,
s.description,
0 resv,
SUM(p.nights) nights,
SUM(p.cancel) cancel,
SUM(p.noshow) noshow,
SUM(p.roomrevenue) roomrevenue,
SUM(p.otherrevenue) otherrevenue
FROM #sp010 p (nolock), #sp010_top t (nolock), history h (nolock), z_source s (nolock)
WHERE h.ghacct = p.chacct AND
p.chacct = t.chacct AND
p.source = s.code
GROUP BY t.grouping, t.chacct, h.name, p.source, s.description
END
ELSE /* By Type or Manager */
-- Write into the final table
INSERT INTO #sp010_final
SELECT (CASE @as_group WHEN 'MANAGER' THEN h.manager
WHEN 'TYPE' THEN h.history_code END) grouping,
p.chacct,
h.name,
p.source,
s.description,
0 resv,
SUM(p.nights) nights,
SUM(p.cancel) cancel,
SUM(p.noshow) noshow,
SUM(p.roomrevenue) roomrevenue,
SUM(p.otherrevenue) otherrevenue
FROM #sp010 p (nolock), history h (nolock), z_source s (nolock)
WHERE h.ghacct = p.chacct AND
p.source = s.code
GROUP BY p.chacct, h.name, h.manager, h.history_code, p.source, s.description
ORDER BY grouping, h.name

-- final the number of visits
UPDATE #sp010_final SET
resv = (SELECT count(*) FROM guest g (nolock)
WHERE g.chacct = r.chacct AND
g.source = r.source AND
--CC03032003 <CC0218> Fix the problem: #REV included all the property instead of the selected property
-- g.status = 'O' AND
g.status in ('O', 'I') AND
--CC03032003 <CC0218> end
g.shareacct IS NULL
--CC03032003 <CC0218> Fix the problem: #REV included all the property instead of the selected property
and g.property like @as_property
--CC03032001 <CC0218> end
--CC11272001 <CC0054> use date range instead of hotel start date
and g.arrival <= @adt_to and g.departure > @adt_from)
--CC11272001 <CC0054> end
+
(SELECT count(DISTINCT g.shareacct) FROM guest g (nolock)
WHERE g.chacct = r.chacct AND
g.source = r.source AND
--CC03032003 <CC0218> Fix the problem: #REV included all the property instead of the selected property
-- g.status = 'O' AND
g.status in ('O', 'I') AND
--CC03032003 <CC0218> end
g.shareacct IS NOT NULL
--CC03032003 <CC0218> Fix the problem: #REV included all the property instead of the selected property
and g.property like @as_property
--CC03032001 <CC0218> end
--CC11272001 <CC0054> use date range instead of hotel start date
and g.arrival <= @adt_to and g.departure > @adt_from)
--CC11272001 <CC0054> end

FROM #sp010_final r (nolock)

-- final result set
/*VL20070328 Insert No Production Records*/
IF @as_no_prod = 'Y'
BEGIN
Set @ls_hostprefix = '%'
if @as_property <> '%'
begin
Select @ls_hostprefix = host_crs_prefix
from z_property_defaults where property = @as_property
end

INSERT INTO #sp010_final
SELECT 99999 'grouping',
g.chacct,
g.name,
g.source ,
g.description,
0 'resv',
0 'nights',
0 'cancel',
0 'noshow',
0 'roomrevenue',
0 'otherrevenue'
from
(
SELECT h.ghacct chacct,
h.name name,
h.source source,
description = ( select description from z_source where code = h.source)
from history h where h.type = 'C' and h.status = 'A' and (h.ghacct like @ls_hostprefix + '%') and h.ghacct not in (SELECT chacct FROM #sp010_final)
) g
order by chacct
END
/*VL20070328 End*/

SELECT * FROM #sp010_final (nolock)

GO

GRANT ALL ON sp_sp010 TO PUBLIC

GO
/****** END Object: Stored Procedure dbo.sp_sp010 Script Date: 24/Mar/2008 10:42 AM ******/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:47:41

but it doesnt have COLLATE in parameter does it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sumantakundu
Starting Member

6 Posts

Posted - 2010-10-21 : 14:44:35

Hi Visakh,

That the issue there is no collate in the parameter and hence I run the SP it takes god knows from where the collatation of SQL_Latine....

Can you help to modify thie SP to take the default collation
you can reach me in email - sumantakundu@yahoo.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 01:54:57

sorry you dont make sense at all. Then when are you getting the above posted error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sumantakundu
Starting Member

6 Posts

Posted - 2010-10-25 : 10:28:33

Hi visakh

when I execute the script on a Db which is Latin1_General_CI_AS

After that when I do sp_help sp010 i find the parameter collation is SQL_Latin1_General_CP1_CI_AS

How do chnage the parameter to Latin1_General_CI_AS

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-10-25 : 11:00:02

The problem is probably that your server collation is different from your DB collation. This means you must define your #temp tables like the following:


-- Create Temp table #sp010
CREATE TABLE #sp010 (
chacct VARCHAR (12) COLLATE DATABASE_DEFAULT NULL,
source VARCHAR (6) COLLATE DATABASE_DEFAULT NULL,
nights INTEGER NULL,
cancel INTEGER NULL,
noshow INTEGER NULL,
roomrevenue NUMERIC(15, 2) NULL,
otherrevenue NUMERIC(15, 2) NULL
)
--etc

sumantakundu
Starting Member

6 Posts

Posted - 2010-10-25 : 14:05:27

Hi Ifor,
Tried the same but still the collation for the parameter after doing sp_help sp_sp010 is sp_help sp_sp010

No change.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources