May 13, 2010 at 2:18 pm
Hi,
I got to work on database with read only permission.
Now i wrote a query with a reusable code in it.
the query will be like
select col1, clo2 from tablename
where col1 = substring(substring(x,charindex('\',x),len(x)),0,charindex('\',substring(x)))
here 'x' is my resusable code which would be like below one.
x= stufff(col3,charindex('.',col3),charindex('.',col3,7),'\')
where col3 will be like \adf.fg.d\jhfl\123\
Now the problem is
i tried to create an user defined function for that resuable code in a database with readonly permissions but it dint work out for me..
so is there any other way other than userdefined functions
May 13, 2010 at 8:58 pm
If you only have READONLY permissions to a database, you can't create objects in the DB. In the future, talk to your DBA about your needs before you decide that's how to solve the problem. Fortunately, a UDF isn't necessary to the solution.
You can use CTEs (common table expressions) to do the same thing you're trying to do with a user-defined function. The CTEs will even run faster because you aren't paying for the overhead of calling a user-defined function. Take a look at books online (SQL Help) and let us know if you have any questions about CTEs.
Rather than generally describing your problem, post up your actual code, sample input data, AND expected results and someone might even show you a tested example about how to do this. A CTE solution would look something like this.
;with cte1 (col1,col2,X) as
(select col1,col2,stufff(col3,charindex('.',col3),charindex('.',col3,7),'\' )
from tablename)
,cte2 as (col1,col2,X,Y) as
(select *,substring(substring(X,charindex('\',X),len(X)),0,charindex('\',substring(X)))
from cte1)
select col1,col2
from cte2
where col1 = Y
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply