Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

how we put this query together

Hi guys, i have a user table,

id    user    
1      bob
2      ted
3      robert

 and this funcion dbo].[Fn_Get] that returns one row of 3 collumns, like this based on the userid.
so if i call the funcion like this : dbo].Fn_Get(1) should return

idl     idb     idc
3        4        5

i want the select to return the users like this.

id    user          idl     idb      idc
1      bob           3        4         5
2      ted            6         3         8
3      robert        4        6        5

so the select query should look like?

select t1.id, t1. from tbuser as t1
0
rafaelrgl
Asked:
rafaelrgl
2 Solutions
 
awking00Commented:
Not real sure of your question. Are you looking for something like this -
select id, user, dbo.Fn_Get(id) from users
0
 
Scott PletcherSenior DBACommented:
I think you need { cross | outer } apply, but not 100% sure.


SELECT
    ut.id, ut.user, fg.idl, fg.idb, fg.idc
FROM dbo.userTable ut
CROSS APPLY dbo.Fn_Get(ut.id) AS fg --Fn_Get is a table-valued function


On SQL 2008, you have some additional options using VALUES() also.
0
 
rafaelrglAuthor Commented:
yes, it's cross aplly, but can we change the function to a procedure. because i can't use newid() on funcion. So can we make the same query you show me but instead of using a function it will be an procedure? can we?
0
 
Scott PletcherSenior DBACommented:
No, you can't use a procedure that way.

Why would you need NEWID() in a function?
0
 
deviprasadgCommented:
In that case add NEWID to the select statement.(select statement with usertable and crossapply on function)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now