parameter problem in sql2005 sp

in sql2005 store procedure is accepting the varchar value which is more than the parameter's varchar value size it is not giving any exception error but removes the remaining character from right side

Example :

Table

-

create table employee

(Empname Varchar(5), EmpNo Int)

Procedure

create procedure test (@.para1 Varchar(5))

as

SELECT * FROM employee WHRE empNAME=@.PARA1

Execution

--

exec test 'ABCDEFGH'

it consider only 'ABCDE' and ignores remaining character without giving any exception error.

Thanks,

It is the specification. Not only in 2005 earlier versions also use the same specification.

But in sql server 2005 you can overcome this issue using Varchar(max) it never truncate the value.

Declare @.A varchar(1), @.B varchar(Max);

Set @.A = '1234' --Truncated value

Set @.B = '1234' --Never Truncate the value

Select @.A, @.B

Note:

The character length is only validated on Insert & Update statement.

|||thanks fo reply

parameter problem in sql2005 sp

in sql2005 store procedure is accepting the varchar value which is more than the parameter's varchar value size it is not giving any exception error but removes the remaining character from right side

Example :

Table

-

create table employee

(Empname Varchar(5), EmpNo Int)

Procedure

create procedure test (@.para1 Varchar(5))

as

SELECT * FROM employee WHRE empNAME=@.PARA1

Execution

--

exec test 'ABCDEFGH'

it consider only 'ABCDE' and ignores remaining character without giving any exception error.

Thanks,

It is the specification. Not only in 2005 earlier versions also use the same specification.

But in sql server 2005 you can overcome this issue using Varchar(max) it never truncate the value.

Declare @.A varchar(1), @.B varchar(Max);

Set @.A = '1234' --Truncated value

Set @.B = '1234' --Never Truncate the value

Select @.A, @.B

Note:

The character length is only validated on Insert & Update statement.

|||thanks fo replysql

Parameter passing from report to SQL

Hi, I have the following SQL code in my DataSet:
(SQL Server 2005)
DECLARE @.sel1 AS varchar(20)
DECLARE @.sel2 AS varchar(20)
DECLARE @.sel3 AS varchar(20)
DECLARE @.sel4 AS varchar(20)
DECLARE @.test AS integer
set @.test = @.TimeDiff; <-- this I want to insert via URL in report
set @.sel1=convert
(char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
set @.sel2=Replace(@.sel1,'-','');
set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
set @.sel4=Replace(@.sel3,'-','');
select @.sel2 AS StartTime, @.sel4 AS EndTime;
I have declared a report parameter (TimeDiff, without @.) in Report
Parameters. It is integer and hidden and has a default value 2 (non queried).
In dataset parameters I do not have any configurations (should I have).
I tried to opass the parameter to my sql code but it is not succeeded.
I got the followng error message:
"Must declare the scalar cariable "@.TimeDiff"
The final meaning is to insert this as parameter via URL and use it in the
dataset sql code, but I can not get it into my SQL .
When running the dataset itself it asks the parameter @.TimeDiff and works OK.
What is wrong in my parameter handling ?Push the ... button (the point button) right beside your dataset
(within the data display), a couple of tabs will popup and goto the
parameters tab.
Within this tab you're able to bind the sql declared parameter to your
reporting parameter.
You'll notice the way it works via your other parameters which are
bound to the sql parameters|||I'd suggest having a stored procedure for this code but as long as you don't
use a temp table (if you use a temp table you must put this in a stored
procedure) then this will work.
I would have expected RS to automatically create a TimeDiff report parameter
to match the @.TimeDiff since @.TimeDiff is not declared. Perhaps it is the
use of the set statement. Try
select @.test = @.TimeDiff
If you click on the ..., parameters tab and there is no @.TimeDiff in the
name column then try adding this by hand and then select your report
parameter that it is mapping to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jarmopy" <jarmopy@.discussions.microsoft.com> wrote in message
news:9123A5B9-0DA9-4639-9884-D9D7A30CAA2C@.microsoft.com...
> Hi, I have the following SQL code in my DataSet:
> (SQL Server 2005)
> DECLARE @.sel1 AS varchar(20)
> DECLARE @.sel2 AS varchar(20)
> DECLARE @.sel3 AS varchar(20)
> DECLARE @.sel4 AS varchar(20)
> DECLARE @.test AS integer
> set @.test = @.TimeDiff; <-- this I want to insert via URL in report
>
> set @.sel1=convert
> (char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
> set @.sel2=Replace(@.sel1,'-','');
> set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
> set @.sel4=Replace(@.sel3,'-','');
> select @.sel2 AS StartTime, @.sel4 AS EndTime;
> I have declared a report parameter (TimeDiff, without @.) in Report
> Parameters. It is integer and hidden and has a default value 2 (non
> queried).
> In dataset parameters I do not have any configurations (should I have).
> I tried to opass the parameter to my sql code but it is not succeeded.
> I got the followng error message:
> "Must declare the scalar cariable "@.TimeDiff"
> The final meaning is to insert this as parameter via URL and use it in the
> dataset sql code, but I can not get it into my SQL .
> When running the dataset itself it asks the parameter @.TimeDiff and works
> OK.
> What is wrong in my parameter handling ?
>
>
>|||Hi, thanks for the answers.
I tried to put the following parameter setting in the dataset (... button)
@.TimeDiff = Parameter!TimeDiff.Value
but now I get the following error message:
The report parameter 'LastDate' has a DefaultValue or a ValidValue that
depends on the report parameter "TimeDiff". Forward dependencies are not
allowed"
The LastDate report parameter is coming from the dataset from field
StartTime and that is calculated from TimeDiff.
LastTime parameter is Hidden and available value is from query and the
default value is from query.
Still something wrong.|||Hi,
I solved the last problem just by changing the order of the parameters in
report side.
Thanks for nicismyname and Bruce L-C|||Hi, you should be aware of the order of your parameters. If lastdate
parameter it's dataset refers to the timediff parameter you should
first prompt the timediff parameter. The prompting order is determined
by the 'report>parameters' menu. You'll notice two arrows at the
dialog popping up after selecting 'report>parameters', use the up and
down arrow to determine the prompting order.

Paging of Records

Hi all,

I have a stored procedure which takes 4 parameters

@.Page Numeric, @.Size Numeric, @.SQL VARCHAR(75), @.COUNT = 0 NUMERIC OUTPUT

In @.SQL variable a SQL querry is passed on which I don't know. Based on this sql querry page and the size I have to return the recordset with limited records and total rows in the fetched from SQL Querry as OUT parameter. Please help me out.

Thanx in advance

AmitI was doing paging like this:

-SP does have parameters - show page#, lines per page;
-create temporary table with identity;
-insert data to temporary table (include sorting);
-select and return page (not big deal if you know page# )
- select and return totals for columns (if it needs)
- output parameter- total pages.

It is very simple and very effective method (it does have potential problems but it works for many cases).|||Hi Snail,

Thank you for your reply. But I think one thing you are missing is that we have a dynamic querry. So please consider this point and help me out.

--Amit|||Originally posted by amitarora7
Hi Snail,

Thank you for your reply. But I think one thing you are missing is that we have a dynamic querry. So please consider this point and help me out.

--Amit
I had dynamic query too. I will try to find code for this sp and post it.

Anyway check this query - how it is possible to create temporary table:

SELECT *,IDENTITY(int, 1, 1) AS newid
INTO #tmp
FROM sysobjects|||Check this code: sp and asp page with paging. It is a little complicated but you could find the main idea.

page splits

Say you have a table as such
create table test
(col1 int identity(1,1) primary key,
col2 varchar(10),
col3 varchar(10))
and now you pound this table with heavy inserts from multiple clients
Can it cause page splits ? It would appear that its inserting in order in
col1 and that pages will be ordered and written sequentially.. If it does
cause page splits, can you explain how ? And also will changing col1 to
nonclustered help ?
ThanksIt should not create page splits on inserts alone but if you update the
varchars later to a value larger than the original it can cause a split.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23B8bqUlFIHA.4308@.TK2MSFTNGP06.phx.gbl...
> Say you have a table as such
> create table test
> (col1 int identity(1,1) primary key,
> col2 varchar(10),
> col3 varchar(10))
> and now you pound this table with heavy inserts from multiple clients
> Can it cause page splits ? It would appear that its inserting in order in
> col1 and that pages will be ordered and written sequentially.. If it does
> cause page splits, can you explain how ? And also will changing col1 to
> nonclustered help ?
> Thanks
>