set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Suyog Dabhole
-- Create date: 19-9-2007
=============================================
ALTER PROCEDURE [dbo].[procGetAllMinibarStatus]
@.AllRooms bit=1, --true if user wants to search for all rooms,false if user does not want to search
@.SingleRoom nvarchar(10)=NULL, --User sends RoomNumber otherwise null
@.ByGroup nvarchar(20)=NULL, --User sends Group name otherwise null
@.ByAreaFrom nvarchar(10)=NULL, --User sends ByAreaFrom otherwise null
@.ByAreaTo nvarchar(10)=NULL, --User sends ByAreaTo otherwise null
@.LockUnlockStatus nvarchar(10)=NULL,
@.CoolerStatus nvarchar(10)=NULL,
@.MinibarStatus nvarchar(10)=NULL,
@.CommunicationStatus nvarchar(10)=NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.strBasicQuery nvarchar(MAX)
SET @.strBasicQuery='SELECT tblRooms.RoomNumber, tblRooms.RoomStatus,
tblMinibars.MinibarDisplayName,
tblModelGrids.ModelGridName,
tblMinibars.MinibarLockStatus,
CASE WHEN tblMinibars.MinibarLockStatus = ''true'' THEN ''~\Images\LockStatusLOCK.gif'' ELSE ''~\Images\LockStatusUNLOCK.gif'' END AS ''MinibarLockStatusImagePath'',
tblMinibars.MinibarCoolerStatus,
CASE WHEN tblMinibars.MinibarCoolerStatus = ''true'' THEN ''MinibarCoolerStatusON.gif'' ELSE ''MinibarCoolerStatusOFF.gif'' END AS ''MinibarCoolerStatusImagePath'',
tblMinibars.MinibarModeStatus, tblMinibars.MinibarOfflineStatus,
CASE WHEN tblMinibars.MinibarOfflineStatus = ''true'' THEN ''OfflineStatusON.gif'' ELSE ''C:\OfflineStatusOFF.gif'' END AS ''MinibarOfflineStatusImagePath'',
tblMinibars.MinibarCoolingStatus,
CASE WHEN tblMinibars.MinibarCoolingStatus = ''true'' THEN ''CoolingStatusAlarmON.gif'' ELSE ''CoolingStatusAlarmOFF.gif'' END AS ''MinibarCoolingStatusImagePath'',
tblMinibars.MinibarDoorOpenStatus,
CASE WHEN tblMinibars.MinibarDoorOpenStatus = ''true'' THEN ''DoorOpenStatus.gif'' ELSE ''DoorOpenStatusOFF.gif'' END AS ''MinibarDoorOpenStatusImagePath'',
tblMinibars.MinibarTooManyDoorOpening,
CASE WHEN tblMinibars.MinibarTooManyDoorOpening = ''true'' THEN ''TooManyDoorOpeningON.gif'' ELSE ''TooManyDoorOpeningOFF.gif'' END AS ''MinibarTooManyDoorOpeningImagePath'',
tblMinibars.MinibarCourtesyXStatus,
CASE WHEN tblMinibars.MinibarCourtesyXStatus = ''true'' THEN ''CourtesyXStatusON.gif'' ELSE ''CourtesyXStatusOFF.gif'' END AS ''MinibarCourtesyXStatusImagePath'',
tblMinibars.MinibarID, tblMinibars.RoomID
FROM tblMinibars INNER JOIN
tblRooms ON tblMinibars.RoomID = tblRooms.RoomID AND tblRooms.IsDeleted = 0 INNER JOIN
tblModelGrids ON tblMinibars.GridID = tblModelGrids.GridID '
IF(@.AllRooms =1)
BEGIN
IF(@.LockUnlockStatus IS NOT NULL or @.CoolerStatus IS NOT NULL or @.MinibarStatus IS NOT NULL or @.CommunicationStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' WHERE '
END
END
ELSE IF(@.SingleRoom IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' WHERE tblRooms.RoomID=(select DISTINCT RoomID from tblRooms where RoomNumber=@.SingleRoom and IsDeleted=0) and tblRooms.RoomStatus=@.RoomStatus'+' and '
END
ELSE IF(@.ByGroup IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' WHERE tblRooms.RoomID IN (SELECT RoomID FROM tblRoomGroups WHERE GroupID=(SELECT DISTINCT GroupID FROM tblGroups WHERE GroupName=@.ByGroup and IsDeleted=0)) and tblRooms.RoomStatus=@.RoomStatus and tblRooms.IsDeleted=0'+' and '
END
ELSE IF((@.ByAreaFrom IS NOT NULL) AND (@.ByAreaTo IS NOT NULL) )
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' WHERE tblRooms.RoomID IN (SELECT DISTINCT RoomID FROM tblRooms WHERE RoomNumber>=@.ByAreaFrom AND RoomNumber<=@.ByAreaTo ) and tblRooms.RoomStatus=@.RoomStatus'+' and '
END
IF(@.LockUnlockStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' MinibarLockStatus='''+@.LockUnlockStatus+''''
IF(@.CoolerStatus IS NOT NULL or @.MinibarStatus IS NOT NULL or @.CommunicationStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+N' and '
END
END
IF(@.CoolerStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' MinibarCoolerStatus='''+@.CoolerStatus+''''
IF(@.MinibarStatus IS NOT NULL or @.CommunicationStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+N' and '
END
END
IF(@.MinibarStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' MinibarModeStatus='''+@.MinibarStatus+''''
IF(@.CommunicationStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+N' and '
END
END
IF(@.CommunicationStatus IS NOT NULL)
BEGIN
SET @.strBasicQuery=@.strBasicQuery+' MinibarOfflineStatus='''+@.CommunicationStatus+''''
END
RETURN @.strBasicQuery
EXEC (@.strBasicQuery)
SET NOCOUNT OFF
END
I am getting following error
Msg 245, Level 16, State 1, Procedure procGetAllMinibarStatus, Line 104
Conversion failed when converting the nvarchar value 'SELECT tblRooms.RoomNumber, tblRooms.RoomStatus,
tblMinibars.MinibarDisplayName,
tblModelGrids.ModelGridName,
tblMinibars.MinibarLockStatus,
CASE WHEN tblMinibars.MinibarLockStatus = 'true' THEN '~\Images\LockStatusLOCK.gif' ELSE '~\Images\LockStatusUNLOCK.gif' END AS 'MinibarLockStatusImagePath',
tblMinibars.MinibarCoolerStatus,
CASE WHEN tblMinibars.MinibarCoolerStatus = 'true' THEN 'MinibarCoolerStatusON.gif' ELSE 'MinibarCoolerStatusOFF.gif' END AS 'MinibarCoolerStatusImagePath',
tblMinibars.MinibarModeStatus, tblMinibars.MinibarOfflineStatus,
CASE WHEN tblMinibars.MinibarOfflineStatus = 'true' THEN 'OfflineStatusON.gif' ELSE 'C:\OfflineStatusOFF.gif' END AS 'MinibarOfflineStatusImagePath',
tblMinibars.MinibarCoolingStatus,
CASE WHEN tblMinibars.MinibarCoolingStatus = 'true' THEN 'CoolingStatusAlarmON.gif' ELSE 'CoolingStatusAlarmOFF.gif' END AS 'MinibarCoolingStatusImagePath',
tblMinibars.MinibarDoorOpenStatus,
CASE WHEN tblMinibars.MinibarDoorOpenStatus = 'true' THEN 'DoorOpenStatus.gif' ELSE 'DoorOpenStatusOFF.gif' END AS 'MinibarDoorOpenStatusImagePath',
tblMinibars.MinibarTooManyDoorOpening,
CASE WHEN tblMinibars.MinibarTooManyDoorOpening = 'true' THEN 'TooManyDoorOpeningON.gif' ELSE 'TooManyDoorOpeningOFF.gif' END AS 'MinibarTooManyDoorOpeningImagePath',
tblMinibars.MinibarCourtesyXStatus,
CASE WHEN tblMinibars.MinibarCourtesyXStatus = 'true' THEN 'CourtesyXStatusON.gif' ELSE 'CourtesyXStatusOFF.gif' END AS 'MinibarCourtesyXStatusImagePath',
tblMinibars.MinibarID, tblMinibars.RoomID
FROM tblMinibars INNER JOIN
tblRooms ON tblMinibars.RoomID = tblRooms.RoomID AND tblRooms.IsDeleted = 0 INNER JOIN
tblModelGrids ON tblMinibars.GridID = tblModelGrids.GridID WHERE tblRooms.RoomID=(select DISTINCT RoomID from tblRooms where RoomNumber=@.SingleRoom and IsDele... <-- What is mean by this?
1) how to increase the size of nvarchar(MAX)
2)I am returning strBasicQuery for purpose of it's value.Here issuse is my query character's extending limit of nvarchar(MAX)
Kindly provide solution for me ASAP
The problem is the RETURN statement. Return is the command to exit the procedure and return an exitcode. The exitcode is defined as INT. So, it's natural that your return command generates the error because the system cannot convert @.strBasicQuery to INT.
Instead of using RETURN @.strBasicQuery, you'd do "print @.strBasicQuery".
|||
Quickest way to increase the size of nvarchar(max) is to use varchar(max). I don't seen any UNICODE characters in your query, so use VarChar. that will DOUBLE the number of characters you can use.
varchar(max) is two times the characters of nvarchar(max)
If you must use nvarchar (we have a lot of kanji stuff for example) you'll have to break it up into muliple queries, sorta like this:
Code Snippet
Create Table #Temp(SomeField int)
declare @.cmd nvarchar(max)
set @.cmd = 'insert into #Temp select 1'
exec(@.cmd)
set @.cmd = 'insert into #Temp select 2'
exec (@.cmd)
select * from #temp
|||
>>Quickest way to increase the size of nvarchar(max) is to use varchar(max). I don't seen any UNICODE characters in your query, so use VarChar. that will DOUBLE the number of characters you can use. <<
nvarchar(max) will hold like a billion characters, which is clearly not being taxed. The problem is, as OJ states, that he is trying to return a character, when RETURN in a stored procedure may only return an integer value
|||
problem is not returning values
kindly see error(end section with red color)
What is mean by IsDeleted...<
I am returning @.strBasicQuery for showing you that finally when i run exec(@.strBasicQuery)
it will not execute because my @.strBasicQuery's final value is end like IsDeleted... which is incomplete.
Kindly see error it showing value of @.strBasicQuery
i know that i have to return int not string
kindly provide solution ASAP
|||
problem is not returning values
kindly see error(end section with red color)
What is mean by IsDeleted...<
I am returning @.strBasicQuery for showing you that finally when i run exec(@.strBasicQuery)
it will not execute because my @.strBasicQuery's final value is end like IsDeleted... which is incomplete.
Kindly see error it showing value of @.strBasicQuery
i know that i have to return int not string
kindly provide solution ASAP
|||
problem is not returning values
kindly see error(end section with red color)
What is mean by IsDeleted...<
I am returning @.strBasicQuery for showing you that finally when i run exec(@.strBasicQuery)
it will not execute because my @.strBasicQuery's final value is end like IsDeleted... which is incomplete.
Kindly see error it showing value of @.strBasicQuery
i know that i have to return int not string
kindly provide solution ASAP
|||
The key to this error is: Conversion failed when converting the nvarchar value and i believe it would finish with to datatype int. This is because RETURN expects and integer value as suggested earlier.
However, I wonder if the reason the string is truncated is due to a limitation in the error handling of SQL. I don't think its anything to do with your SQL string being truncated. To test, comment out your EXEC statement and follow the guys advice previously and change:
RETURN @.strBasicQuery
to be:
PRINT @.strBasicQuery
This will give you the SQL String you are trying to execute.
HTH!
|||First, RETURN is expecting an int. You cannot return a string. Change RETURN to PRINT.
Second, the "IsDeleted..." is not your problem. The error message only displays the first 1024 characters of the command.
Third, your real problem is the EXEC command does not access varchar(max), yes I know it says it does, it but doesn't. You need to break your command into multiple varchar(4000) strings and add them together into an EXEC. Like this:
DECLARE @.sql1 varchar(4000), @.sql2 varchar(4000), @.sql3 varchar(4000)
SET @.sql1 = 'SELECT *'
SET @.sql2 = ' FROM TABLEA'
SET @.sql3 = ' WHERE A=A'
EXEC (@.sql1+@.sql2+@.sql3)
|||
 Tom Phillips wrote: |
| |
Third, your real problem is the EXEC command does not access varchar(max), yes I know it says it does, it but doesn't. You need to break your command into multiple varchar(4000) strings and add them together into an EXEC. Like this:
|
Tom, that is not correct. Execute can work with varchar(max) just fine. Here is an example for you.
Code Snippet
declare @.var1 varchar(8000), @.var2 varchar(8000), @.sql varchar(max)
set @.var1 = replicate('A',8000)
set @.var2 = replicate('B',8000)
set @.sql = 'select len(''' + cast(@.var1 as varchar(max)) + cast(@.var2 as varchar(max)) + ''')'
exec(@.sql)
|||
Suyog, the problem is the RETURN. Again, you cannot return a string, only INT.
The reason you see the last word "IsDeleted" is because the error message is truncated. It's by no mean cause in this case.
Here is a quick example to stress the point.
Code Snippet
create proc bad
as
declare @.ret varchar(max)
set @.ret = 'I am a string'
return @.ret
go
create proc good
as
declare @.ret int
set @.ret = 999
return @.ret
go
--this will never work!
declare @.return varchar(max)
exec @.return=bad
select @.return [bad]
go
declare @.return int
exec @.return=good
select @.return [good]
go
drop proc bad,good
go
|||oj,
I keep hearing that, and your code works. However, every time I try it in production, it fails. I don't know what the cause is, except if I change it to multiple varchars, instead of varchar(max), it works fine.
|||
| Tom Phillips wrote: |
| |
| oj, I keep hearing that, and your code works. However, every time I try it in production, it fails. I don't know what the cause is, except if I change it to multiple varchars, instead of varchar(max), it works fine. |
You're more than likely hitting "data type precedence" issue.
Let's take a look at previous example, if I forget to convert() to varchar(max), my example would be failing too.
Code Snippet
declare @.var1 varchar(8000), @.var2 varchar(8000), @.sql varchar(max)
set @.var1 = replicate('A',8000)
set @.var2 = replicate('B',8000)
set @.sql = 'select len(''' + @.var1 + @.var2 + ''')'
exec(@.sql)
More info on data type precedence:
http://msdn2.microsoft.com/en-us/library/ms190309.aspx
