How to change a string

Hi,

I have a string like (Mike,David,John...). How can I change this string into a proper syntax of

('Mike','David','John'...) so that I can use it in an "IN" statement some where else.

Select * from TableName where Customer_Name In ('Mike','david','john',...)

Any function? Any Idea?

Thanks

An easier method might be to create a parameter called CustomerName and then use the following code:

Select * from TableName where Customer_Name IN(@.CustomerName)

|||

won't work, he needs to put the single quote on them...

Search for a user on this forum called Lisa Nicholls. she posted a solution for someone with a similiar issue, She even posted the code to use.

|||

With my example he would not have to worry about the quotes as the SQL operation addresses internally using the parameter. I have several of my reports built this way which allows me to avoid the quote issue.

|||

Thanks every one for the feed back. I have created the below function and it work fine.

CreateFUNCTION [dbo].[fn_StringWithQuotes] (@.InputString Varchar(8000))

RETURNSnvarchar(4000)

AS

BEGIN

DECLARE @.IDTable TABLE(Item Varchar(255))

DECLARE @.item VARCHAR(255)

WHILE(DATALENGTH(@.InputString)> 0)ANDRIGHT(SUBSTRING(@.InputString,1,CHARINDEX(',',@.InputString)),1)=','

BEGIN

SET @.item =SUBSTRING(@.InputString,1,(CHARINDEX(',', @.InputString)-1))

INSERTINTO @.IDTable(Item)VALUES(@.Item)

SET @.InputString =SUBSTRING(@.InputString,(CHARINDEX(',', @.InputString)+1),DATALENGTH(@.InputString))

END

INSERTINTO @.IDTable(Item)VALUES(@.InputString)

DECLARE @.NAME VARCHAR(255)

DECLARE @.NEW_NAME VARCHAR(255)

SET @.NEW_NAME=''

SELECT @.NEW_NAME = @.NEW_NAME +''','''+A.ITEM FROM(SELECT ITEM FROM @.IDTable)A

SET @.NEW_NAME=RIGHT(@.NEW_NAME,LEN(@.NEW_NAME)-2)+''''

RETURN @.NEW_NAME

END

How to CASE a SmallInt to a Varchar value

Why does this syntax work for bit but not for smallint ?
-- This works OK - Setting = BIT
CASE (dbo.tblAssessment.Setting)
WHEN 1 THEN 'Internal'
WHEN 0 THEN 'External'
END AS Setting,
-- This fails - error converting value 'N/A' to column of datatype smallint
-- Credit = SMALLINT
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN 'N/A'
ELSE dbo.tblAssessment.Credit
END AS Credit,
Thanks.Hi
It is expecting to return a smallint as one of the ELSE's in a smallint.
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN CONVERT(CHAR(10), 'N/A' )
ELSE CONVERT(CHAR(10), dbo.tblAssessment.Credit)
END AS Credit
Regards
Mike
"hals_left" wrote:

> Why does this syntax work for bit but not for smallint ?
>
> -- This works OK - Setting = BIT
> CASE (dbo.tblAssessment.Setting)
> WHEN 1 THEN 'Internal'
> WHEN 0 THEN 'External'
> END AS Setting,
> -- This fails - error converting value 'N/A' to column of datatype smallin
t
> -- Credit = SMALLINT
> CASE (dbo.tblAssessment.Credit)
> WHEN 101 THEN 'N/A'
> ELSE dbo.tblAssessment.Credit
> END AS Credit,
> Thanks.
>

How to call a stored proc?

What is the syntax to call a SQL server stored procedure from a report?
Say if I wanted to create a custom field that would be based on an
expression that calls the stored proc (which would return a single
value from a single field - a sum in this case).
What would be the syntax for doing this within a UDF?
Thanks for any help!Hi
it very similar with sending an sql statment.
from the new dataset window choose command type=storsd procedure, and press
OK.
now you will get the stored procedures list. select the wanted SP and run
the query.
now, go back to the dataset window, you will find there your SP parameters.
you can give some values to those parameters.
hope it was helpful
Shlomi
"megabyte" wrote:
> What is the syntax to call a SQL server stored procedure from a report?
> Say if I wanted to create a custom field that would be based on an
> expression that calls the stored proc (which would return a single
> value from a single field - a sum in this case).
> What would be the syntax for doing this within a UDF?
> Thanks for any help!
>|||Thanks for your reply - I think maybe I did not explain this well
enough: The place where I need to call the stored proc is not while
building the original datasets for the report - this stored proc needs
to run during the execution of the report, for each record returned by
one of the datasets - so really the best place for me to call it would
be in the custom "code" section of the report, in a
User-Defined-Function - do you know what the syntax would be for that?
(this stored proc would return one single value, a sum).
Shlomi wrote:
> Hi
> it very similar with sending an sql statment.
> from the new dataset window choose command type=storsd procedure, and press
> OK.
> now you will get the stored procedures list. select the wanted SP and run
> the query.
> now, go back to the dataset window, you will find there your SP parameters.
> you can give some values to those parameters.
>
> hope it was helpful
> Shlomi
>
> "megabyte" wrote:
> > What is the syntax to call a SQL server stored procedure from a report?
> > Say if I wanted to create a custom field that would be based on an
> > expression that calls the stored proc (which would return a single
> > value from a single field - a sum in this case).
> >
> > What would be the syntax for doing this within a UDF?
> >
> > Thanks for any help!
> >
> >|||Hi
I can think about two ways to do this:
1. you can write a custom code for reading from DB, and call it from the
development envionment.
2. you can retrive all data and write client function for dealing the
aggregations. (i did dit once by retrive an XML and write some XML function
to deal with the aggregation).
if you need the exact solution, i'll try to hel you.
Shlomi
"megabyte" wrote:
> Thanks for your reply - I think maybe I did not explain this well
> enough: The place where I need to call the stored proc is not while
> building the original datasets for the report - this stored proc needs
> to run during the execution of the report, for each record returned by
> one of the datasets - so really the best place for me to call it would
> be in the custom "code" section of the report, in a
> User-Defined-Function - do you know what the syntax would be for that?
> (this stored proc would return one single value, a sum).
>
> Shlomi wrote:
> > Hi
> > it very similar with sending an sql statment.
> > from the new dataset window choose command type=storsd procedure, and press
> > OK.
> > now you will get the stored procedures list. select the wanted SP and run
> > the query.
> > now, go back to the dataset window, you will find there your SP parameters.
> > you can give some values to those parameters.
> >
> >
> > hope it was helpful
> >
> > Shlomi
> >
> >
> > "megabyte" wrote:
> >
> > > What is the syntax to call a SQL server stored procedure from a report?
> > > Say if I wanted to create a custom field that would be based on an
> > > expression that calls the stored proc (which would return a single
> > > value from a single field - a sum in this case).
> > >
> > > What would be the syntax for doing this within a UDF?
> > >
> > > Thanks for any help!
> > >
> > >
>|||Yes, 1 is what I'm after - writing custom code for reading from the DB
at runtime and calling it from the dev. environment - that's exactly
what I don't know how to do - it's VB.Net language, right? I have no
idea how to do that - do you? Do you know of sample code out there?
Shlomi wrote:
> Hi
> I can think about two ways to do this:
> 1. you can write a custom code for reading from DB, and call it from the
> development envionment.
> 2. you can retrive all data and write client function for dealing the
> aggregations. (i did dit once by retrive an XML and write some XML function
> to deal with the aggregation).
> if you need the exact solution, i'll try to hel you.
> Shlomi
> "megabyte" wrote:
> > Thanks for your reply - I think maybe I did not explain this well
> > enough: The place where I need to call the stored proc is not while
> > building the original datasets for the report - this stored proc needs
> > to run during the execution of the report, for each record returned by
> > one of the datasets - so really the best place for me to call it would
> > be in the custom "code" section of the report, in a
> > User-Defined-Function - do you know what the syntax would be for that?
> > (this stored proc would return one single value, a sum).
> >
> >
> > Shlomi wrote:
> > > Hi
> > > it very similar with sending an sql statment.
> > > from the new dataset window choose command type=storsd procedure, and press
> > > OK.
> > > now you will get the stored procedures list. select the wanted SP and run
> > > the query.
> > > now, go back to the dataset window, you will find there your SP parameters.
> > > you can give some values to those parameters.
> > >
> > >
> > > hope it was helpful
> > >
> > > Shlomi
> > >
> > >
> > > "megabyte" wrote:
> > >
> > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > Say if I wanted to create a custom field that would be based on an
> > > > expression that calls the stored proc (which would return a single
> > > > value from a single field - a sum in this case).
> > > >
> > > > What would be the syntax for doing this within a UDF?
> > > >
> > > > Thanks for any help!
> > > >
> > > >
> >
> >|||Hi
This MSDN article will tell you everything you want to know about it:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
if you have more questions, you will be more than welcome.
P.S.
Use the Microsoft enterprise library to read your data.
"megabyte" wrote:
> Yes, 1 is what I'm after - writing custom code for reading from the DB
> at runtime and calling it from the dev. environment - that's exactly
> what I don't know how to do - it's VB.Net language, right? I have no
> idea how to do that - do you? Do you know of sample code out there?
>
> Shlomi wrote:
> > Hi
> > I can think about two ways to do this:
> > 1. you can write a custom code for reading from DB, and call it from the
> > development envionment.
> >
> > 2. you can retrive all data and write client function for dealing the
> > aggregations. (i did dit once by retrive an XML and write some XML function
> > to deal with the aggregation).
> >
> > if you need the exact solution, i'll try to hel you.
> >
> > Shlomi
> >
> > "megabyte" wrote:
> >
> > > Thanks for your reply - I think maybe I did not explain this well
> > > enough: The place where I need to call the stored proc is not while
> > > building the original datasets for the report - this stored proc needs
> > > to run during the execution of the report, for each record returned by
> > > one of the datasets - so really the best place for me to call it would
> > > be in the custom "code" section of the report, in a
> > > User-Defined-Function - do you know what the syntax would be for that?
> > > (this stored proc would return one single value, a sum).
> > >
> > >
> > > Shlomi wrote:
> > > > Hi
> > > > it very similar with sending an sql statment.
> > > > from the new dataset window choose command type=storsd procedure, and press
> > > > OK.
> > > > now you will get the stored procedures list. select the wanted SP and run
> > > > the query.
> > > > now, go back to the dataset window, you will find there your SP parameters.
> > > > you can give some values to those parameters.
> > > >
> > > >
> > > > hope it was helpful
> > > >
> > > > Shlomi
> > > >
> > > >
> > > > "megabyte" wrote:
> > > >
> > > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > > Say if I wanted to create a custom field that would be based on an
> > > > > expression that calls the stored proc (which would return a single
> > > > > value from a single field - a sum in this case).
> > > > >
> > > > > What would be the syntax for doing this within a UDF?
> > > > >
> > > > > Thanks for any help!
> > > > >
> > > > >
> > >
> > >
>

How to call a function from a column formula in my MS SQL table

Good day!

What is the syntax on calling a function from a column formula in an MS SQL table.

I created a table, one column's value will be coming from a function. And at the same time, I will pass parameters to the function. How do I do this? Is this correct?

SELECT dbo.FunctionName([Parameter1, Parameter2])

But i can't save the table, "Error validating the formula".

Pls. help
Thanks a lot.<edit> Never mind, I misunderstood what you are doing.
I'm afraid I have no advice.|||It may or may not work depending on what you are trying to do. You can use a udf and define the result as simply:

DEFAULT (dbo.udfMyFunction('SomeParam','OtherParam'))

However, SomeParam and OtherParam must be constants or system functions (like suser_sname() or host_name()). They can't be names of columns in your table.

Regards,

hmscott|||Or perhaps he want a computed column:

create table foo (
id int,
hash as dbo.getHash(id),
...
);