problem in Function ?

Create FUNCTION FUNCTION_NAT

(

@.F_BRANCH_CODE CHAR,

@.F_COUNTRY CHAR,

@.F_CLIENT_VALUE CHAR

)

RETURNS TABLE

AS

RETURN

(

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = @.F_BRANCH_CODE

AND

COUNTRY = @.F_COUNTRY

AND

CLIENT_VALUE = @.F_CLIENT_VALUE

)

This is my function . when i run it as follows

select * from FUNCTION_NAT('450','British','BRI')

i did not get any value it is empty .

But when i run this query

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = '450'

AND

COUNTRY = 'British'

AND

CLIENT_VALUE = 'BRI'

it works fine . What is the problem in my function .

My knee-jerk reaction is that this might be a problem with the type definitions of the function parameters; hold on and I will try to verify. Look at this:

Code Snippet

alter FUNCTION FUNCTION_NAT
(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)
RETURNS TABLE
AS
RETURN
( select @.f_branch_code as branch_code,
@.f_country as country,
@.f_client_value as client_value
)

go

select * from function_nat('450','British','BRI')

/*
branch_code country client_value
-- -
4 B B
*/

You need to alter your type definitions from this

Code Snippet

(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)

to something like this:

Code Snippet

(
@.F_BRANCH_CODE CHAR(xx),

@.F_COUNTRY CHAR(yy),
@.F_CLIENT_VALUE CHAR(zz)
)

where xx, yy, and zz are the maximum number of characters that will be passed through each argument.

|||

Kent - i think you've hit the nail on the head.

From BOL: When n is not specified in a data definition or variable declaration statement, the default length is 1

SQL will not throw an error but merely cut the string off at the length. From the look of at least one of your variables, you may be better off with the VARCHAR datatype.

HTH!

sql

Problem in Dataset

Hi

I created a stored procedure which returns a dataset
that is passed to crystal report like this

dset = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "spr_CR_Demo2")

dset.Tables(0).TableName = "spr_CR_Demo2"

crystal.SetDataSource(dset)
CrystalReportViewer2.ReportSource = crystal

But only one record get displayed,how can i do it
anyone can help me

ThanxAre you sure that sp returns more than one record?
Open the report and Do Verify database. Save the report and Try it again

problem getting "LIKE @parameter%" to work

Hello,

I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.

SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'

It works fine in when i type it in manually.

But im using a stored procedure from VS and it will not work with the '%' part

SELECT *
FROM table
WHERE column_name LIKE @.parameter%

Any help or ideas would be greatly appreciated.Hi, my similar line looks like ...

Dim myCommand = New SqlCommand("exec search_telephone '%" & filterValue1 & "%'", myConnection)

... where search_telephone is a stored procedure expecting an input of part of a surname.

NOTE the 2 percentage characters.

Richard|||Like uses a string as its input. So you'd need to use '%' + @.param + '%'|||Is your SQL running inside a stored proc? That's what it sounds like to me.

If that's the case, do something like this:

declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''' + @.parameter + ''''
EXEC ( @.strSQL )

I do this all the time in my stored procs for searches. I haven't found another way to do this. The trick is getting the number of single quotes right.|||It should be:

declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''%' + @.parameter + '%'''
EXEC ( @.strSQL )

but you get the point.

For help debugging these types of "dynamically generated" SQL statements, use PRINT ( @.strSQL ) and run it in query analyzer.|||in your stored procedure this should work and will not require SELECT permissions on the table like an EXEC(@.sql) would.

CREATE PROC [some_search]
 @.Search nvarchar(50)
AS
 Declare @.LikeSearch nvarchar(52)
 -- you could also add do '%' + @.Search + '%' depending on how you want the search to work
 SET @.LikeSearch = @.Search + '%'
 SELECT *
 FROM table
 WHERE column_name LIKE @.LikeSearch

sql