Can we declare a Table in a FUNCTION.................?

Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.
> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.
|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................
|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?

> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.

> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?

> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.

> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.

> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.

Can we declare a Table in a FUNCTION.................?

Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?

> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.

> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?

> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.

> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.

> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.sql

Can we declare a Table in a FUNCTION.................?

Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?
> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.
> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?
> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.
> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.
> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.

Can this be done with an output parameter?

Hi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.

My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.

How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.

Thanks::How could I create an output parameter that would return their new user id and then how
::would i set it to an integer variable

do you mean from the front end or inside the stored proc ?

if you mean the stored proc:

create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as
insert into ( ....) values (...) select @.userid=@.@.IDENTITY
..

HTH|||Use the OUTPUT Parameter of the Stored Procedure. Refer to SQL Server BOL for further assitance ...|||Hi yeah I mean by using the output param in the sp.

How does the @.@.Idendity work then?

How would i set this to a varialble in my front end. Would I use

Dim UserID as Integer

Dim objParam as New SqlParameter("@.@.Idendity", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objParam.Value = UserID
objComm.Parameters.Add(objParam)

Thanks|||First, you should use SCOPE_IDENTITY(), not @.@.IDENTITY.

Next, use a SP like this:

create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as
insert into ( ....) values (...)
select @.userid=SCOPE_IDENTITY()

then

Dim objParam as New SqlParameter("@.UserID", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objComm.Parameters.Add(objParam)

Then after the command is run, check objComm.Parameters("@.UserID") for the UserID|||Many thanks Douglas. We may have to start calling you superman on here lol as you come to everyones rescue.|||Thanks. I do have the glasses, though no cape, and no one wants to se me in tights<g>.

Can terms lookup take into account the US/english spelling differences ?

Is it possible for the terms lookup function to manage the differences between US and english
spelling ? For example if I search for the terms "color" and "categorization", I'd would like that the terms lookup also count the "colour" and "categorisation" occurences in the text.

Thanks
VincentTake a look at the SOUNDEX() function. It does a similar thing by converting a string to a 4 char string/number. You will get lots more hits than expected normally.

I have setup search functions which search for exactly matches and if none are found, use soundex() to find similar matches.|||Thanks ! I'll dig into that

Can someone helpl me write this query to create a crosstab(piv

Hi Rob,
Originally I was sending the data and using a function in VB.Net to create
the pivot table, but I'm trying to have the work done on the SQL Server
instead of transferring all that data to the ASP.Net page. I think at the
moment that it's duplicating some of the work in the query but was wondering
if someone knew of a better way to write the query.
David
"Rob Farley" wrote:

> Presumably you're going to display your results somewhere, such as in
> ReportingServices, or a web page, etc... So why not add them up there inst
ead
> if you're worried?
> Personally, I'm not too keen on the PivotTable concept of SQL. I would
> rather return all the data, and then use something at the presentation lay
er
> to turn it into a PivotTable.
>Just to be sure, could you lay out the output that you are looking for'
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
> "Rob Farley" wrote:
>|||There's no pretty way of returning a PivotTable directly from SQL. You can d
o
it through a large amount of temporary table population or creating an ugly
piece of 'dynamic' SQL. But honestly, it's much easier to do it once you've
got the data away from SQL.
If you group by all your columns and rows, so that you get only one record
back for each cell of your PivotTable, then you can easily handle that in
VB.Net. If you're worried about the amount of data passed back, you could
group by IDs instead of column/row names, and pass back separate datasets
which translate the IDs into the more human-readable form.
If you're really determined to create a stored procedure that will do it for
you, then I'm sure we can come up with something, but please, pick the
'simple' solution, which is to find a control that will display the
PivotTable for you.
Rob
"David Reynolds" wrote:

> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David

Can report services use Request.QueryString("varName") for record selection?

I know the parameters are available, but let's say if I've got a variable in a request.QueryString() function I want to use from a page which links to the report, is it possible for the record selection to be filtered by the Request.QueryString() variable?You need to pass the page parameters into as report parameter name / value pairs. We do not provide you the raw page querystring in the report (as the report execution isn't even guaranteed to come from a web page).

Can not run script.

Hi,
I have to scripts, one is use to create table and
another one is use to create user define function. I want
to check why i can run the script to create table but i
can not run the script to create user define function. Is
it got any restriction in security? What i can check? Is
it security problem?
Any suggestion of where i can check?
Any help is much appreciated.
regards,
florenceHi,
To execute it directly m you have to be in any one of the below database
roles,
1. db_owner
2. db_ddladmin
SA can also give the user exclusive "Create FUNCTION" prev.
Grant Create Function to username
Thanks
Hari
MCDBA
"florence" <florencelee@.visualsolutions.com.my> wrote in message
news:906b01c3ea41$7cd6eda0$a001280a@.phx.gbl...

quote:

---

> Hi,
> I have to scripts, one is use to create table and
> another one is use to create user define function. I want
> to check why i can run the script to create table but i
> can not run the script to create user define function. Is
> it got any restriction in security? What i can check? Is
> it security problem?
> Any suggestion of where i can check?
> Any help is much appreciated.
> regards,
> florence

---